How do i make this Excel Macro recording repeat itself on the next lines until there is no more data

Question

I am fairly new to VBA what i am hopping to do is to make this macro recording code repeat this on the Cell 1 then Cell 2 Then Cell 3 until there is no more data fields left in the A col. Basically its supposed to copy a Cell in A1 and copy to book 2 then hit refresh data then copy some Info back into Book1 and repeat from A1 until there is no more cells with data left in the A column

I have tried to read to do this online but couldnt figure it out

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A2").Select
Selection.Copy
Windows("Book2").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.RefreshAll
Windows("Book1").Activate
Range("F2").Select
Windows("Book2").Activate
Range("K6").Select
Selection.Copy
Windows("Book1").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
    :=False, Transpose:=False
Windows("Book2").Activate
Range("L6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
    :=False, Transpose:=False
Range("G3").Select
End Sub

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Apr 01 '19 at 06:41

Tim Williams · Answer 1 · 2019-04-01T18:33:29.810

1

You can do something like this:

Sub Macro1()
    Dim c As Range, sht2 As Worksheet

    'adjust sheet names as required    
    Set c = Workbooks("Book1").Sheets("Sheet1").Range("a2")
    Set sht2 = Workbooks("Book2").Sheets("Sheet1")

    Do while Len(c.value) > 0

        sht2.Range("A2").Value = c.value
        sht2.Parent.RefreshAll

        'fixed source cells
        c.EntireRow.Cells(6).Value = sht2.Range("K6").Value
        c.EntireRow.Cells(7).Value = sht2.Range("L6").Value 

        '...or last populated cells in K,L
        c.EntireRow.Cells(6).Value = sht2.Cells(rows.count, "K").End(xlUp).Value
        c.EntireRow.Cells(7).Value = sht2.Cells(rows.count, "L").End(xlUp).Value 

        Set c = c.Offset(1, 0) 'next cell down
    Loop


End Sub

edited Apr 01 '19 at 18:33

answered Mar 31 '19 at 05:49

Tim Williams

154,628
8
97
125

Thank you so much for this, Could you make it that it looks for the last cell in column K and L and not just Cell K6 And L6? – Travel and adventure Inc Apr 01 '19 at 18:27
Thank you so much for this you made my day!! – Travel and adventure Inc Apr 02 '19 at 19:03
No problem - note that the "last row" approach assumes there's content in both K and L (if one was missing you'd get results from different rows). Just in case that's important. – Tim Williams Apr 02 '19 at 20:45

How do i make this Excel Macro recording repeat itself on the next lines until there is no more data

1 Answers1