How can I copy data from multiple tabs to one tab?

Question

I am trying to copy data from multiple tabs to one single tab. The data need to be filtered first then copied from different tabs to a new tab. Data from different tabs (has random number of lines)should be continuous within the new tab. Due to the size of the data, it is divided into multiple tabs. So merging tabs into one tab first is not an option.

I have below difficulties that need help:

From second tab, I don’t need to copy the header of data. Any command can be added to the code?
Current codes not copying all four tabs, I am not too sure what is the issue
Can my active sheet be a general command instead of specific like ActiveSheet.Range("$A$1:$U$493692")?

See below code

Sub Filter_FSI()
'
' Filter_FSI Macro
'

'
    Dim lastRow As String

    lastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1

    Sheets("Train 3-8").Select
    ActiveSheet.Range("$A$1:$U$493692").AutoFilter Field:=4, Criteria1:="FSI"
    ActiveSheet.AutoFilter.Range.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Sheets("Sheet1").Paste
    Sheets("Train 9-14").Select
    ActiveSheet.Range("$A$1:$U$539243").AutoFilter Field:=4, Criteria1:="FSI"
    ActiveSheet.AutoFilter.Range.Copy
    Sheets("Sheet1").Select
    Range("A" & lastRow).Select
    ActiveSheet.Paste
    Sheets("Train 15-25").Select
    ActiveSheet.Range("$A$1:$U$528028").AutoFilter Field:=4, Criteria1:="FSI"
    ActiveSheet.AutoFilter.Range.Copy
    Sheets("Sheet1").Select
    Range("A" & lastRow).Select
    ActiveSheet.Paste
    Sheets("Train 27-41").Select
    ActiveSheet.Range("$A$1:$U$298055").AutoFilter Field:=4, Criteria1:="FSI"
    ActiveSheet.AutoFilter.Range.Copy
    Sheets("Sheet1").Select
    Range("A" & lastRow).Select
    ActiveSheet.Paste
    Sheets("Sheet1").Copy
    Windows("Train Data JULY_Sam Edit.xlsb").Activate
End Sub

Note: I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this to your code. It will make your code much more stable and reliable. — Pᴇʜ, Aug 14 '18 at 14:42

score 0 · Answer 1 · answered Aug 14 '18 at 15:14

So a couple things I noticed with your code - you're declaring lastrow as a string, but that should really be a long since it's representing a number.

Personally, I'm not a fan of autofiltering - and like Peh said above, you want to avoid using Select and Copy/Paste when you can. Try this solution below - it's my personal preference of doing things. We loop through all your worksheets, then loop through every cell in Column D - if it is equal to "FSI", we bring it to Sheet1:

Option Explicit
Sub Filter_FSI()

Dim sht As Worksheet, sht2 As Worksheet
Dim lastrow As Long, i As Long, j As Long, k As Long
Dim myworksheets As Variant

Set sht = ThisWorkbook.Worksheets("Sheet1")
myworksheets = Array("Train 3-8", "Train 9-14", "Train 15-25", "Train 27-41")

'Bring in headers
sht.Range("A1:U1").Value = Worksheets("Train 3-8").Range("A1:U1").Value
k = 2

For i = 0 To UBound(myworksheets)
    Set sht2 = Worksheets(myworksheets(i))
    lastrow = sht2.Cells(sht2.Rows.Count, 4).End(xlUp).Row

    For j = 2 To lastrow
        If sht2.Cells(j, 4).Value = "FSI" Then
            sht.Range("A" & k & ":U" & k).Value = sht2.Range("A" & j & ":U" & j).Value
            k = k + 1
        End If
    Next j
Next i

End Sub

How can I copy data from multiple tabs to one tab?

1 Answers1