Copy data from one sheet to another using VBA

June 10, 2011 at 04:06:01
Specs: Windows Vista
Hi, in excel 2007 trying to loop through (un-fixed length) column (say C) and where row value matches (say "High"), then transfer value of cells Dx and Bx to sheet "transfer", where x is the row# where the matches are found. Assume "transfer" exists.

See More: Copy data from one sheet to another using VBA

Report •


#1
June 10, 2011 at 06:53:45
Do you know how to modify VBA code if we gave you a skeleton or do you need a "complete" solution written for you?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 10, 2011 at 07:00:20
Hi DerbyDad; seems by way of iteration the following will do the trick:

Sub test()
Dim LR As Long, i As Long
With ActiveSheet
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("C" & i).Value = "High" Then
.Range("B" & i).Copy
Sheets("Transfer").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
.Range("D" & i).Copy
Sheets("Transfer").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If
Next i
End With
End Sub


Report •

#3
June 10, 2011 at 07:58:04
As should this, but possibly in a more efficient manner.

Sub UsingFind()
Dim LR As Long, c As Variant
 With ActiveSheet
  LR = .Range("C" & Rows.Count).End(xlUp).Row
   With .Columns("C")
    Set c = .Find("High", lookat:=xlWhole)
     If Not c Is Nothing Then
      firstAddress = c.Address
        Do
         nxtRw = _
           Sheets("Transfer").Range("B" & Rows.Count).End(xlUp).Row + 1
            Sheets("Transfer").Range("B" & nxtRw) = Range("B" & c.Row)
            Sheets("Transfer").Range("D" & nxtRw) = Range("D" & c.Row)
          Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
     End If
   End With
 End With
End Sub

The reason I say "more efficient" is three-fold:

By using the .Find method, the code does not have to check every cell to see if it contains "High". It simply "Finds" it.

By using nxtRw = Sheets("Transfer").Range("B" & Rows.Count).End(xlUp).Row + 1 the code only has to calculate the next open row once per iteration, as opposed to twice by having to figure out the "offset" each time.

By eliminating the Copy-Paste functions, it's, well, it's simply more efficient.

Granted, in such simple code, the efficiencies might not even be seen, but as a "best practice" it stills makes sense to use the most efficient methods, at least in my opinion.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 10, 2011 at 13:27:43
DerbyDad - much appreciated and i see your points. I am fully in favour of identifying the most efficient way and yours certainly match that description; i am grateful for your inputs and clearly even "simple" situations leaves the door open for ingenuity. :-)

Report •

Ask Question