Many tabs to 1

[Zubair]

Dear Experts,

I have flight sales for each day in a separate tab and the end of the month I need to bring all tabs in Summary Tab.

Need help to please with some VBA code please.

Shown below as a sample 2 days tabs and a summary tab.

Flights.xlsx
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	1	1009	Emirates	Cash	EK-687	60023	67800
3	1	1006	Saudi	Cash	SA-712	60024	50000
4	1	1008	German	Credit	LH-890	60025	76000
5	1	1005	Pakistan	Credit	PK-214	60026	80000
6	2	1009	Emirates	Cash	EK-687	60027	74000
7	2	1006	Saudi	Cash	SA-712	60028	82000
8	2	1008	German	Credit	LH-890	60029	98000
9	2	1005	Pakistan	Credit	PK-214	60030	69000
Flight summary

Flights.xlsx
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	1	1009	Emirates	Cash	EK-687	60023	67800
3	1	1006	Saudi	Cash	SA-712	60024	50000
4	1	1008	German	Credit	LH-890	60025	76000
5	1	1005	Pakistan	Credit	PK-214	60026	80000
Flight day 1

Flights.xlsx
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	2	1009	Emirates	Cash	EK-687	60027	74000
3	2	1006	Saudi	Cash	SA-712	60028	82000
4	2	1008	German	Credit	LH-890	60029	98000
5	2	1005	Pakistan	Credit	PK-214	60030	69000
Flight day 2

 

[Michael M]

Maybe like this

Sub MM1()
 Dim lr As Long, ws As Worksheet
 lr = Sheets("Flight Summary").Cells(Rows.Count, "A").End(xlUp).Row
     For Each ws In Worksheets
            If ws.Name <> "Flight Summary" Then
                lr2 = ws.Cells(Rows.Count, "A").End(xlUp).Row
                 ws.Range("A2:G" & lr2).Copy Sheets("Flight Summary").Range("A" & lr)
                lr = Sheets("Flight Summary").Cells(Rows.Count, "A").End(xlUp).Row
            End If
    Next ws
End Sub

 

[JEC]

Use Power Query. Assuming you have 1 table on every sheet, you can do it with one line of Power Query language.
Important is to use real tables instead of ranges.

let
    Source = Table.Combine(List.Skip(Excel.CurrentWorkbook()[Content]))
in
    Source

Book1
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	1	1009	Emirates	Cash	EK-687	60023	67800
3	1	1006	Saudi	Cash	SA-712	60024	50000
4	1	1008	German	Credit	LH-890	60025	76000
5	1	1005	Pakistan	Credit	PK-214	60026	80000
Flight day 1

Book1
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	2	1009	Emirates	Cash	EK-687	60027	74000
3	2	1006	Saudi	Cash	SA-712	60028	82000
4	2	1008	German	Credit	LH-890	60029	98000
5	2	1005	Pakistan	Credit	PK-214	60030	69000
Flight day 2

Result:

Book1
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	1	1009	Emirates	Cash	EK-687	60023	67800
3	1	1006	Saudi	Cash	SA-712	60024	50000
4	1	1008	German	Credit	LH-890	60025	76000
5	1	1005	Pakistan	Credit	PK-214	60026	80000
6	2	1009	Emirates	Cash	EK-687	60027	74000
7	2	1006	Saudi	Cash	SA-712	60028	82000
8	2	1008	German	Credit	LH-890	60029	98000
9	2	1005	Pakistan	Credit	PK-214	60030	69000
Sheet1

 

[Zubair]

Use Power Query. Assuming you have 1 table on every sheet, you can do it with one line of Power Query language.
Important is to use real tables instead of ranges.

let
    Source = Table.Combine(List.Skip(Excel.CurrentWorkbook()[Content]))
in
    Source

Book1
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	1	1009	Emirates	Cash	EK-687	60023	67800
3	1	1006	Saudi	Cash	SA-712	60024	50000
4	1	1008	German	Credit	LH-890	60025	76000
5	1	1005	Pakistan	Credit	PK-214	60026	80000
Flight day 1

Book1
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	2	1009	Emirates	Cash	EK-687	60027	74000
3	2	1006	Saudi	Cash	SA-712	60028	82000
4	2	1008	German	Credit	LH-890	60029	98000
5	2	1005	Pakistan	Credit	PK-214	60030	69000
Flight day 2

Result:

Book1
	A	B	C	D	E	F	G
1	Day No.	Customer No.	Customer Name	Credit / Cash Sale	FLT. NO.	Receipt No.	Quantity
2	1	1009	Emirates	Cash	EK-687	60023	67800
3	1	1006	Saudi	Cash	SA-712	60024	50000
4	1	1008	German	Credit	LH-890	60025	76000
5	1	1005	Pakistan	Credit	PK-214	60026	80000
6	2	1009	Emirates	Cash	EK-687	60027	74000
7	2	1006	Saudi	Cash	SA-712	60028	82000
8	2	1008	German	Credit	LH-890	60029	98000
9	2	1005	Pakistan	Credit	PK-214	60030	69000
Sheet1

Also a good option to use Table, but the data operations team is sending is not in a table, 1st I need to convert all tabs in table than apply your given formula, which is time consuming, but I will definitely use this formula in some other similar cases.

 

[Dave Patton]

You could use VStack.

I do not know your sheet names.
You could put a blank sheet named Start in front of the relevant sheets and a blank sheet named End after all the relevant sheets.
Then a formula something like =VSTACK(Start:End!A2:M6) ; edit as appropriate.

The formula can be expanded to remove blanks.

 

[Zubair]

Maybe like this

Sub MM1()
 Dim lr As Long, ws As Worksheet
 lr = Sheets("Flight Summary").Cells(Rows.Count, "A").End(xlUp).Row
     For Each ws In Worksheets
            If ws.Name <> "Flight Summary" Then
                lr2 = ws.Cells(Rows.Count, "A").End(xlUp).Row
                 ws.Range("A2:G" & lr2).Copy Sheets("Flight Summary").Range("A" & lr)
                lr = Sheets("Flight Summary").Cells(Rows.Count, "A").End(xlUp).Row
            End If
    Next ws
End Sub

Hi Michael,

I am getting the following result:

Missing:

1. Heading in "Flight Summary"
2. Row 5 of "Flight day 1"

What mistake I am doing ? I have copy/paste same VBA code

Flights.xlsx
	A	B	C	D	E	F	G
1	1	1009	Emirates	Cash	EK-687	60023	67800
2	1	1006	Saudi	Cash	SA-712	60024	50000
3	1	1008	German	Credit	LH-890	60025	76000
4	2	1009	Emirates	Cash	EK-687	60027	74000
5	2	1006	Saudi	Cash	SA-712	60028	82000
6	2	1008	German	Credit	LH-890	60029	98000
7	2	1005	Pakistan	Credit	PK-214	60030	69000
Flight Summary

 

[Michael M]

@Zubair
Sorry, I assumed your Summary page already had a header row.
Try this

Sub MM1()
 Dim lr As Long, lr2 as long, ws As Worksheet
Sheets("Flight Day 1") range("A1:G1") copy Sheets("Flight Summary").Range("A1")
 lr = Sheets("Flight Summary").Cells(Rows.Count, "A").End(xlUp).Row
     For Each ws In Worksheets
            If ws.Name <> "Flight Summary" Then
                lr2 = ws.Cells(Rows.Count, "A").End(xlUp).Row
                 ws.Range("A2:G" & lr2).Copy Sheets("Flight Summary").Range("A" & lr)
                lr = Sheets("Flight Summary").Cells(Rows.Count, "A").End(xlUp).Row+1
            End If
    Next ws
End Sub

 

[Michael M]

Oops typo

Sub MM1()
 Dim lr As Long, lr2 as long, ws As Worksheet
Sheets("Flight Day 1") range("A1:G1") copy Sheets("Flight Summary").Range("A1")
 lr = Sheets("Flight Summary").Cells(Rows.Count, "A").End(xlUp).Row
     For Each ws In Worksheets
            If ws.Name <> "Flight Summary" Then
                lr2 = ws.Cells(Rows.Count, "A").End(xlUp).Row
                 ws.Range("A2:G" & lr2).Copy Sheets("Flight Summary").Range("A" & lr+1)
                lr = Sheets("Flight Summary").Cells(Rows.Count, "A").End(xlUp).Row
            End If
    Next ws
End Sub