Looping imports through different sheets

Question

I am a newbie to VBA but got tasked to work with it anyway. So my task is to build a macro that takes data from different sheets and puts it below each other in one result sheet ("Tabelle1" in my example). The input data in each sheet is stored in blocks of two columns, right next to each other - so columns A and B have to import into the result sheet, then C and D and so on. Doing this for one sheet is not a problem:

Sub Makro1()
    '
    ' Makro1 Makro
    '

    Dim Erste As Long
    Dim k As Long
    Dim j As Long

    k = 1
    j = 2

    Do
        Sheets("Tabelle1").Select
        Erste = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row

        Sheets("Tabelle2").Select
        Range(Cells(5, k), Cells(5, j)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Tabelle1").Select
        Cells(Erste, 3).Select
        ActiveSheet.Paste

        k = k + 2
        j = j + 2

    Loop Until Sheets("Tabelle2").Cells(4, k).Value = ""

End Sub

But I not only have one input sheet ("Tabelle2" in this example) but several (up until sheet20 or so). And all of them are built the exact same way, only with different data in each. What I would need the macro to do is, when reaching the empty cell in the first input sheet ("Tabelle2), go to the next input sheet ("Tabelle3") and continue the import of the data.

It doesn't sound too hard to do at first, but I cannot seem to find a solution. If anyone could help me out, it would be very much appreciated :-)

I know that the macro itself is very badly written and I can get rid of most of the Select. But as long as it works I'm fine.

Instead of using the name of the sheet, use the `Index` - `Sheet(1)` and `Sheet(2)` etc - You can use that as a variable that you can increment. — braX, Sep 12 '19 at 21:15
I build another loop around the original loop, increasing the index number of the input sheet with every rotation and it seemed to work on a small scale. Will check tomorrow if it works with the real data set. Thanks buddy. — n_urb, Sep 12 '19 at 21:47
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ᴇʜ, Sep 13 '19 at 06:04
Thank you @braX, got the code to work with your little hint. Would mark it as solved, but I am afraid I can not since you only replied as comment? Thank you nevertheless. — n_urb, Sep 13 '19 at 09:25

score 0 · Accepted Answer · answered Sep 13 '19 at 09:50

Instead of using the name of the sheet, use the Index

Example: Sheet(1) and Sheet(2) etc

You can use that number as a variable that you can increment.

Example:

Dim i as Integer
Sheet(i).Select

Note:

It is also better practice to change the code to not rely on .Select as it can cause confusion and problems.

In addition, it would be better to use Worksheet(1) as charts can also be referred to as sheets.

Looping imports through different sheets

1 Answers1