Help! Excel macro to copy values from one worksheet to another

[andre0170]

Hi... well I'm stuck. I'm hoping that someone can help me identify where I'm going wrong with the macro code I'm using...I'm working on a spreadsheet that tracks purchases. When the word "Data" is listed in Column B of sheet 1 (Instruments_TradeCalc), I want to have the values of four cells in the same row copied from Sheet1 (Instruments_TradeCalc) onto the next available row of Sheet2 (RecordPositions). The cells contain a formula so I'm looking to copy the value, not the formula - not sure if this is even possible.

I've gotten part of the way there in that I think the code is close. It doesn't return an error, but no data is copied to the subsequent work sheet (the Message Box at the end even returns "0 rows of matching data have been copied.").

I can't get it to copy and paste the data. Can someone help?

Here's what I'm currently working with:

Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim Counter As Integer

On Error GoTo Err_Execute

'Start search in row 3
LSearchRow = 3

'Start copying data to row 2 in RecordPositions (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0
With Sheets("Instruments_TradeCalc")
'If value in column B = "Data", copy entire row to RecordPositions
If .Range("B" & LSearchRow) = "Data" Then
Counter = Counter + 1

'Select row in Instruments_TradeCalc to copy and then Paste
.Range("B" & LSearchRow).Resize(1, 10).Copy Destination:=Sheets("RecordPositions").Range("B" & LCopyToRow)

'Move counter to next row
LCopyToRow = LCopyToRow + 1
End If

LSearchRow = LSearchRow + 1
End With
Wend

'Position on cell A3
Application.CutCopyMode = False
Sheets("Instruments_TradeCalc").Select
Range("A3").Select

MsgBox Counter & " rows of matching data have been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

[andre0170]

@andre0170
ok, just figured out how to get it to paste. however, still pasting formulas, not values so getting #ref errors. might have to do with paste special instead of paste... any thoughts, or code to use VB to paste special?

[andre0170]

well, its pasting now... but only for data represented by LSearchRow = 3. AND its only pasting in the other workbook to Row 2 - it keeps overwriting.

I have 4 rows of data to search - they are specific at 3, 8, 13, and 15. The data found here should be copied and pasted to the other workbook, each in the next available row... and so on. Also, what is being pasted still contains formulas, so I am getting #ref errors. Need tounderstand how to copy and paste values only, if possible... thank you!