Looping Copy and Paste VBA

[Johndinho]

Hi,

I am trying to automate an HR report and have a list of names I want to copy and paste 1 at a time into a predefined report.

I've got the VBA sorted for the distribution and all the formulae set to update the report but the copy and paste has me completely stumped.

Names are in Sheet1!A1:A50

I want VBA to copy Sheet1!A1 to Sheet2!A1 then run the distribution code
then
Copy Sheet1!A2 to Sheet2!A1 run the code
Copy Sheet1!A3 to Sheet2!A1 run the code
etc

Over time the 50 names in Sheet1!A1:A50 will grow so i know there needs to be stop when the last name is copied,pasted and distributed.

Some help would be great

Thanks

John

 

[JackDanIce]

Try:

Sub CopyLoop()

    Dim x       As Long
    Dim LR      As Long
    
    Application.ScreenUpdating = False
        
    LR = Sheets("Sheet1").Cells(Rows.count, 1).End(xlUp).row

    For x = 2 To LR
        Sheets("Sheet2").Cells(1, 1).Value = Sheets("Sheet1").Cells(x, 1).Value
        'Run code
    Next x
    
    Application.ScreenUpdating = True

End Sub

 

[Johndinho]

Absolutely Fantastic...........

Really wish I understood a bit more about loops

THANK YOU!!!!!!

 

[JackDanIce]

You're welcome - Loops aren't difficult, in above, you're looping from 2 to the last row number and changing the row argument inside the cell object:

For x = 2 to LR
  Sheets("Sheet2").Cells(1, 1).Value = Sheets("Sheet1").Cells(x, 1).Value
Next x

Arguments to cell object are: Cells(row number, column number) so cells(1, 1) is A1, cells(2, 1) is A2 etc until LR where LR is the row number of the last filled cell on column A.

Finally it's a the .Value property of the cell object you're changing so can avoid copy and paste too.