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

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • andre0170
    New Member
    • Apr 2010
    • 3
    #1

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

    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_Tr adeCalc), I want to have the values of four cells in the same row copied from Sheet1 (Instruments_Tr adeCalc) onto the next available row of Sheet2 (RecordPosition s). 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("Instrum ents_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_Tra deCalc to copy and then Paste
    .Range("B" & LSearchRow).Res ize(1, 10).Copy Destination:=Sh eets("RecordPos itions").Range( "B" & LCopyToRow)

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

    LSearchRow = LSearchRow + 1
    End With
    Wend

    'Position on cell A3
    Application.Cut CopyMode = False
    Sheets("Instrum ents_TradeCalc" ).Select
    Range("A3").Sel ect

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

    Exit Sub

    Err_Execute:
    MsgBox "An error occurred."
    Tags: None
    • andre0170
      New Member
      • Apr 2010
      • 3
      #2
      Originally posted by 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_Tr adeCalc), I want to have the values of four cells in the same row copied from Sheet1 (Instruments_Tr adeCalc) onto the next available row of Sheet2 (RecordPosition s). 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("Instrum ents_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_Tra deCalc to copy and then Paste
      .Range("B" & LSearchRow).Res ize(1, 10).Copy Destination:=Sh eets("RecordPos itions").Range( "B" & LCopyToRow)

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

      LSearchRow = LSearchRow + 1
      End With
      Wend

      'Position on cell A3
      Application.Cut CopyMode = False
      Sheets("Instrum ents_TradeCalc" ).Select
      Range("A3").Sel ect

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

      Exit Sub

      Err_Execute:
      MsgBox "An error occurred."
      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?

        Comment

        • andre0170
          New Member
          • Apr 2010
          • 3
          #3
          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!

            Comment

            Previous template Next
            Working...