Tab Specific VBA Loop

Question

new to VBA. I’m trying to create a VBA loop code to run through a series of tabs and perform my code on each tab and basically export data into a central tab in my worksheet called “database”.

My file is setup so users can add additional “tasks” and thus I will have multiple tabs named: Task, Task (2), Task (3), Task (4), Task (5) . . etc

My VBA terminology is poor, however in excel basic terms I am trying to

-IF tab name (Starts with “TASK”)

-THEN (Perform my code in that particular TASK tab)

-AND (paste user input data from that tab into my “database” tab into a NEW ROW Starting A2)

Hopefully this makes sense (Task tab gets pasted in cell A2 of “database” tab) (Task (2) gets pasted into cell A3 of “database” tab) (the Sheet reference will need to change as “task” will change to task (2) + Task (3) etc.

'My Code
 Sheets("Task").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Database").Select
Range("A2").Select
**'This will need to change each time I paste a new tab in (A2 > A3 > A4)**
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
Sheets("External (Client) Output").Select

Possible duplicate of [copying range of cells and pasting to the first empty row more effective code? Excel](http://stackoverflow.com/q/17972106/11683) — GSerg, Nov 10 '16 at 00:59

score 1 · Answer 1 · answered Nov 10 '16 at 10:56

you can try this

Option Explicit

Sub main()
    Dim ws As Worksheet
    Dim iTab As Long
    Dim baseRng As Range

    Set baseRng = Worksheets("Database").Range("A2")
    For Each ws In Worksheets
        If Left(ws.Name, 4) = "TASK" Then
            With ws
                With .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
                    baseRng.Offset(iTab).Resize(, .Rows.Count).Value = Application.Transpose(.Value)
                End With
                iTab = iTab + 1
            End With
        End If
    Next ws
End Sub

@Alex, did you get through it? – user3598756 Nov 19 '16 at 08:58 — user3598756, Nov 19 '16 at 08:58

cullan · Accepted Answer · 2016-11-10T03:20:48.890

0

So what I'm suggesting is the following: 1. Iterate through each worksheet 2. Check if that worksheet's name is Database, if it is, then skip it 3. Select the data within that worksheet (ie. it will loop through Task1, Task2, etc) 4. Paste that data in your Database worksheet (keep track of a count so that you know which row that task data is being placed into)

Dim row as Integer
Dim ws as Range
row = 2
For Each ws In ThisWorkbook.Sheets
    If (Not ws.Name = "Database") Then
        With ws
            .Range(Range("A2"), .Range("A2").End(xlDown)).Select
            Worksheets("Database").Range(Cells(row, 1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
            row = row + 1
        End With
    End If
Next ws

edited Nov 10 '16 at 03:20

answered Nov 10 '16 at 03:14

cullan

280
3
13

If there are other sheets i.e "database" "summary" "Client Output". Can i just add them into the Not line? – Alex Nov 10 '16 at 03:17
Yes you can, just change it to: If (Not ws.Name = "Database" And Not ws.Name = "Summary" And Not ws.Name = "Client Output") – cullan Nov 10 '16 at 03:18
no problem, glad i can help! please upvote if you like the answer :) – cullan Nov 10 '16 at 04:59
1

you should change `ThisWorkbook.Sheets` to `ThisWorkbook.Worksheets` and be sure you're looping through actual `Worksheet` objects and not `Chart` ones where a `Range` property would fail. Furthermore change `Worksheets("Database").Range(Cells(row, 1))` to `Worksheets("Database").Range(Worksheets("Database").Cells(row, 1))`, to be sure `Cells` is actually referring to worksheet "Database" and not relying on the implicit reference to the _active_ sheet – user3598756 Nov 10 '16 at 07:35
Big VBA noob guys, so bare with me please. I'm very keen to learn. Sub UpdateOutputMacro() ' ' UpdateOutputMacro Macro ' ' 'START MACRO Dim row As Integer Dim ws As Range row = 2 For Each ws In ThisWorkbook.Sheets If (Not ws.Name = "Database") Then With ws .Range(Range("A2"), .Range("A2").End(xlDown)).Select Worksheets("Database").Range(Cells(row, 1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True row = row + 1 End With End If Next ws End Sub – Alex Nov 11 '16 at 04:03

Tab Specific VBA Loop

2 Answers2