Stack Exchange
Selecting rows until empty cell
I am attempting to create a macro that will pull data from several sheets and display them in an 'OVERVIEW' sheet.
At the moment I have the following:
Sheets("Sheet1).Select
ActiveCell.Range("A1:G7").Select
SELECTION.Copy
Sheets("OVERVIEW").Select
ActiveCell.Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveCell.Range("A1:G7").Select
Application.CutCopyMode = False
SELECTION.Copy
Sheets("OVERVIEW").Select
ActiveCell.Offset(7, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
ActiveCell.Range("A1:G2").Select
Application.CutCopyMode = False
SELECTION.Copy
Sheets("OVERVIEW").Select
ActiveCell.Offset(7, 0).Range("A1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-12
ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False

Unfortunately, this currently only copies the data from the first sheet. I would much rather have something along the lines of the following pseudo code
sub COPY1()
Selection = []
curentRow = 1
while(notEmpty(cell(AcurentRow)))
    Selection.add(curentRow)
    curentRow++
return Selection
End Sub

sub PASTE1(selection)
curentRow=1
while(notEmpty(cell(AcurentRow)))
    curentRow++

paste(selection)
End Sub


You really need to learn how to avoid using .Select.

What @K.Dᴀᴠɪs says, plus End(xlup) and 'End(xldown)`.

You can loop through the sheets, and it will skip over "OVERVIEW"
Sub Button1_Click()
    Dim ws As Worksheet, sh As Worksheet, LstRw As Long
    Set ws = Sheets("OVERVIEW")
    For Each sh In Sheets
        If sh.Name <> ws.Name Then
            With sh
            LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
                .Range("A1:G" & LstRw).Copy
                ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            End With
        End If
    Next sh
    Application.CutCopyMode = False
End Sub


Thank you very much! This does exactly what I needed it to do

Edited the Range to "A2:G" as the first row of each sheet already contains the headers, and it is doing exactly what I had hoped for.

sub copy_to_overview()

    currentRow = 1

    while (notempty(cell(currentrow))

      currentrow.copy

      sheet("overwiev").currentrow.paste
      currentrow = currentrow + 1

    wend

end sub


IMO there's no reason not to use Copy. it only "clashes" with a function of a Range object so the syntax for calling either are completely different. I never get why people are unsure about using variable names like row when it's often the best description of what the variable actually represents! I would add that names of subs should be capitalised though. Not least because otherwise, in this example, you end up with intellisense making Range.Copy => Range.copy (note the lowercase c)

I'm getting a compilation issue on the while statement. This is definitely the way forward though. Thank you very much
