Selecting rows until empty cell

Question

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`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — K.Dᴀᴠɪs, Feb 07 '18 at 16:06
What @K.Dᴀᴠɪs says, plus `End(xlup)` and 'End(xldown)`. — SJR, Feb 07 '18 at 16:06

Yuri Molodyko · Answer 1 · 2018-02-07T16:24:11.553

2

sub copy_to_overview()

    currentRow = 1

    while (notempty(cell(currentrow))

      currentrow.copy

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

    wend

end sub

edited Feb 07 '18 at 16:24

answered Feb 07 '18 at 16:11

Yuri Molodyko

590
6
20

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) – CallumDA Feb 07 '18 at 16:17
I'm getting a compilation issue on the while statement. This is definitely the way forward though. Thank you very much – Feb 07 '18 at 16:20

score 2 · Accepted Answer · answered Feb 07 '18 at 16:15

2

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

answered Feb 07 '18 at 16:15

Davesexcel

6,896
2
27
42

Thank you very much! This does exactly what I needed it to do – Feb 07 '18 at 16:26
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. – Feb 07 '18 at 16:28

Selecting rows until empty cell

2 Answers2