Help me selecting VBA Loop through All Sheets Except the main Sheet

[djmnon]

I have a Main Sheet containing orders which has 10000 records

1. I need to create 3 separate sheets by selecting some filters on the main sheet (Master). This I have already done by creating a Macro and performing the steps needed.
2. Now from the 3 seperate sheets I need to combine them and paste it in a single sheet. So for eg :- 1st sheet has 5000 records, 2nd has 3000 and 3rd has 1500 record

I want to combine these into one sheet append and paste it so 5000 then from 5001 the 3000 records from second sheet will be appended and a final sheet will be created.

I have managed to do this but I'm not able to specify the sheets where the VBA should select the records only from specific sheets and append them together. Its also adding the records from the Master sheet. Please help

Below is the code :-

Option Explicit
Public Sub CombineDataFromAllSheets()

    Dim wksSrc As Worksheet, wksDst As Worksheet
    Dim rngSrc As Range, rngDst As Range
    Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
    
    'Notes: "Src" is short for "Source", "Dst" is short for "Destination"
    
    'Set references up-front
    Set wksDst = ThisWorkbook.Worksheets("Sheet8")
    lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below (and in Toolbelt)!
    lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below (and in Toolbelt)!
    
    'Set the initial destination range
    Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
    
    'Loop through all sheets
    For Each wksSrc In ThisWorkbook.Sheets
    
    
        'Make sure we skip the "Import" destination sheet!
        If wksSrc.Name <> "Sheet8" Then
            
            'Identify the last occupied row on this sheet
            lngSrcLastRow = LastOccupiedRowNum(wksSrc)
            
            'Store the source data then copy it to the destination range
            With wksSrc
                Set rngSrc = .Range(.Cells(1, 21), .Cells(lngSrcLastRow, lngLastCol))
                rngSrc.Copy Destination:=rngDst
            End With
            
            'Redefine the destination range now that new data has been added
            lngDstLastRow = LastOccupiedRowNum(wksDst)
            Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
            
        End If
    
    Next wksSrc

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last row
'OUTPUT      : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
        End With
    Else
        lng = 1
    End If
    LastOccupiedRowNum = lng
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last column
'OUTPUT      : Long, the last occupied column
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedColNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByColumns, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Column
        End With
    Else
        lng = 1
    End If
    LastOccupiedColNum = lng
End Function

 

[mumps]

This macro will create a sheet named "Combined" and paste the data from the three sheets. You don't need the functions you posted. It also assumes the main sheet is named "Master". You can change that sheet name in the code if necessary. It also assumes that you have only four sheets, the Master and the three sheets you created.

Sub CombineDataFromAllSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Combined"
    Set desWS = Sheets("Combined")
    For Each ws In Sheets
        If ws.Name <> "Master" And ws.Name <> "Combined" Then
            With desWS
                ws.UsedRange.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
        End If
    Next ws
    Application.ScreenUpdating = False
End Sub

 

[djmnon]

I checked on this it works but again its taking data from the main sheet too.

Let me give you an example probably I wasnt clear earlier

The above are the tabs

I have created 4 Macros to get data from Project data sheet to the Sheet2 and then to Sheet1

So what im expecting my new Macro to do is to capture All the rows/data from Sheet1 and Sheet2 and combine and put in a New Sheet (Naming convention can be checked later)

Note :- It should ignore Project Data tab and the Instructions Tab

Hope I could help better

 

[mumps]

Try:

Sub CombineDataFromAllSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Combined"
    Set desWS = Sheets("Combined")
    For Each ws In Sheets(Array("Sheet1", "Sheet2"))
        With desWS
            ws.UsedRange.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    Next ws
    Application.ScreenUpdating = False
End Sub

 

[djmnon]

Thanks a ton it worked for me

 

[djmnon]

There is another concern I'm facing. In these records that I have below is the scenario

The above text remains the same which is

I have a Main Sheet containing orders which has 10000 records

1. I need to create 3 separate sheets by selecting some filters on the main sheet (Master). This I have already done by creating a Macro and performing the steps needed.
2. Now from the 3 seperate sheets I need to combine them and paste it in a single sheet. So for eg :- 1st sheet has 5000 records, 2nd has 3000 and 3rd has 1500 record

I want to combine these into one sheet append and paste it so 5000 then from 5001 the 3000 records from second sheet will be appended and a final sheet will be created.

But there is one column called g5 date . Though all the filters are pretty much standard and I have automated everything in Macro. There is the g5 date field which is Dynamic.

Looks something like this in the drop down

now the issue is this is selected based on the financial year Example: FY21 will consist of O/N/D from CY21 plus JAN-SEP CY22

Now I have recorded the Macro and selected ones needed for this month but when we move into April I'm not sure if my recorded Macro will capture that...Is there a way to make this Dynamic? so that every month that's gonna come is added in the Macro automatically?

Let me know if its unclear

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of the appropriate sheets.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).