Consolidate tables on multiple worksheets to one worksheet

I am looking for some help on this issue. I am using VBA in Excel 2007 to consolidate tables from five separate worksheets onto one worksheet. This works great in a stand alone workbook, but I need to move this into a workbook with other tabs that I will not consolidate data from. I tried creating an array as a varaible which included the five workbooks I need to consolidate, but I could not get it to work. Here is the code I am using as a separate process that works:

Sub SummariseData()
    Dim x As Long, llastrow As Long, lfirstrow As Long
    Range("Data").CurrentRegion.Offset(1, 0).ClearContents
    Application.ScreenUpdating = False
    For x = 1 To Sheets.Count
        If Sheets(x).CodeName <> "Sheet1" Then
            If Sheets(x).Range("A2") <> "" Then
                lfirstrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
                llastrow = Sheets(x).Range("A1").End(xlDown).Row
                Sheets(x).Range("A2:N" & llastrow).Copy Destination:=Sheet1.Range("B" &          Rows.Count).End(xlUp).Offset(1, 0)
                Sheet1.Range("A" & lfirstrow & ":A" & lfirstrow + llastrow - 2) = Sheets(x).Name
            End If
        End If
    Next x

End Sub

Can anoyone help with how to write the code to specifically call the the five tabs I need to consolidate, instead of loop through all tabs in the workbook? The tab names are T1, T2, T3, T4, T5 all consolidaing to a tab call Summary.

Answers


You can try this:

Dim sh As Worksheet, ws As Worksheet
Set ws = Thisworkbook.Sheets("Summary")

For Each sh in Thisworkbook.Sheets(Array("T1","T2","T3","T4","T5"))
    If sh.Range("A2").Value <> "" Then
        sh.Range("A1",sh.Range("A" & Rows.Count).End(xlUp).Offset(0,14).Address).Copy _
        ws.Range("B" & Rows.Count).End(xlUp).Offset(1,0)
        ws.Range(Range("A" & Rows.Count).End(xlUp).Offset(1,0).Address, _
            Range("B" & Rows.Count).End(xlUp).Offset(0,-1).Address).Value = sh.Name
    End If
Next

Not tested though so i leave it to you.


Try to replace with this :

Sub SummariseData()
    Dim x As Long, llastrow As Long, lfirstrow As Long

    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Summary")


    Range("Data").CurrentRegion.Offset(1, 0).ClearContents
    Application.ScreenUpdating = False
    For x = 1 To 5
        sheetName = "T" & x
        Set ws2 = Worksheets(sheetName)

        If ws2.Range("A2") <> "" Then
            lfirstrow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
            llastrow = ws2.Range("A1").End(xlDown).Row
            ws2.Range("A2:N" & llastrow).Copy Destination:=ws1.Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
            ws1.Range("A" & lfirstrow & ":A" & lfirstrow + llastrow - 2) = sheetName
        End If
    Next x

End Sub

Details on what I changed :

  1. I used worksheet objects instead of calling the worksheet by its name each time (ws1 for the worksheet TO which you're copying, and ws2 for the worksheet FROM which you're copying.
  2. I used a loop that goes from 1 to 5, to concatenate that value to a T, in order to get the tab name.

Need Your Help

Create feature vector programmatically in Spark ML / pyspark

python apache-spark pyspark apache-spark-ml

I'm wondering if there is a concise way to run ML (e.g KMeans) on a DataFrame in pyspark if I have the features in multiple numeric columns.

With Laravel, can you change a column name as part of a migration?

php laravel

Pretty much just what the title says; I need to change a column name as part of a migration, can that be done and if so, how?