Consolidate sheets horizontally i.e. side by side

[Ramneek]

Dear Sir,

I download a report in excel from a software. The report contains different tabs each containing details on quarterly basis. I want to consolidate all the tabs in one sheet to create a yearly summary but i want to append them side by side and not on the top of each other. The report looks like this

37AACCB6390F1ZN_GSTR3BR1_RECONCILED_Summary(2020-2021).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
4	Section	Particulars		April						May						June						Quarter - 1
5				Taxable	IGST	CGST	SGST	Cess	Total	Taxable	IGST	CGST	SGST	Cess	Total	Taxable	IGST	CGST	SGST	Cess	Total	Taxable	IGST	CGST	SGST	Cess	Total
6	GSTR-3B Supply Details	3.1(a) - Outward taxable supplies (Other than zero rated, nil rated and exempted)		₹8,49,125.00	₹0.00	₹76,422.00	₹76,422.00	₹0.00	₹10,01,969.00	₹8,75,838.00	₹0.00	₹78,826.00	₹78,826.00	₹0.00	₹10,33,490.00	₹8,75,838.00	₹0.00	₹78,826.00	₹78,826.00	₹0.00	₹10,33,490.00	₹26,00,801.00	₹0.00	₹2,34,074.00	₹2,34,074.00	₹0.00	₹30,68,949.00
7		3.1(b) - Outward taxable supplies (zero rated)		₹0.00	₹0.00			₹0.00	₹0.00	₹0.00	₹0.00			₹0.00	₹0.00	₹0.00	₹0.00			₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
8		3.1(c) - Other outward supplies (nil rated, exempted)		₹0.00					₹0.00	₹0.00					₹0.00	₹0.00					₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
9		3.1(e) - Non GST outward supplies		₹0.00					₹0.00	₹0.00					₹0.00	₹0.00					₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
10		3.2 - Inter-state supplies		₹0.00	₹0.00				₹0.00	₹0.00	₹0.00				₹0.00	₹0.00	₹0.00				₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
11		Total from GSTR-3B (A)		₹8,49,125.00	₹0.00	₹76,422.00	₹76,422.00	₹0.00	₹10,01,969.00	₹8,75,838.00	₹0.00	₹78,826.00	₹78,826.00	₹0.00	₹10,33,490.00	₹8,75,838.00	₹0.00	₹78,826.00	₹78,826.00	₹0.00	₹10,33,490.00	₹26,00,801.00	₹0.00	₹2,34,074.00	₹2,34,074.00	₹0.00	₹30,68,949.00
12	GSTR-1 Supply Details	B2B Supplies		₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
13		B2C Small Supplies		₹8,49,125.00	₹0.00	₹76,421.25	₹76,421.25	₹0.00	₹10,01,967.50	₹8,75,838.00	₹0.00	₹78,825.42	₹78,825.42	₹0.00	₹10,33,488.84	₹8,75,838.00	₹0.00	₹78,825.42	₹78,825.42	₹0.00	₹10,33,488.84	₹26,00,801.00	₹0.00	₹2,34,072.09	₹2,34,072.09	₹0.00	₹30,68,945.18
14		B2C Large Supplies		₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
15		Exports		₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
16		Credit/Debit Notes - Registered		₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
17		Credit/Debit Notes - Unregistered		₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
18		Advance Receipts		₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
19		Tax Adjustments		₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
20		Nil Supplies		₹0.00					₹0.00	₹0.00					₹0.00	₹0.00					₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00	₹0.00
21		Total from GSTR-1 (B)		₹8,49,125.00	₹0.00	₹76,421.25	₹76,421.25	₹0.00	₹10,01,967.50	₹8,75,838.00	₹0.00	₹78,825.42	₹78,825.42	₹0.00	₹10,33,488.84	₹8,75,838.00	₹0.00	₹78,825.42	₹78,825.42	₹0.00	₹10,33,488.84	₹26,00,801.00	₹0.00	₹2,34,072.09	₹2,34,072.09	₹0.00	₹30,68,945.18
22		Difference (A - B)		₹0.00	₹0.00	₹1.00	₹1.00	₹0.00	₹2.00	₹0.00	₹0.00	₹1.00	₹1.00	₹0.00	₹1.00	₹0.00	₹0.00	₹1.00	₹1.00	₹0.00	₹1.00	₹0.00	₹0.00	₹2.00	₹2.00	₹0.00	₹4.00
23
Q1 - APR-JUN

Cell Formulas
Range	Formula
O12:O20,O6:O10,I12:I20,I6:I10,AA12:AA20,U12:U20,U6:U10,AA6:AA10	U6	=SUM(P6:T6)
V12:Z20,V6:Z10	V6	=SUM(D6,J6,P6)
D11:AA11	U11	=SUM(U6:U9)
D21:AA21	D21	=SUM(D12:D20)
D22:AA22	D22	=ROUND(SUM(D11-D21), 0)

Named Ranges
Name	Refers To	Cells
rone_04	='Q1 - APR-JUN'!$D$12:$H$20	D21, V12, I12
rone_05	='Q1 - APR-JUN'!$J$12:$N$20	J21, V12, O12
rone_06	='Q1 - APR-JUN'!$P$12:$T$20	P21, U12:V12
rthrb_04	='Q1 - APR-JUN'!$D$6:$H$10	D11, V6, I6
rthrb_05	='Q1 - APR-JUN'!$J$6:$N$10	J11, V6, O6
rthrb_06	='Q1 - APR-JUN'!$P$6:$T$10	P11, U6:V6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D22:AA23	Cell Value	=0	text	NO
D22:AA23	Cell Value	<>0	text	NO

 

[Peter_SSs]

1. When the report comes from the other software into Excel, is there anything in rows 1:3 (you haven't shown those rows) and, if so, should that come from each sheet to the consolidated sheet too?
   
   
2. Do you want a new worksheet with all data transferred to it or would it suffice to use the existing left hand sheet as a starting point and just append the data from the other sheets at the right of its data?

 

[Ramneek]

Sir,

1. There is no data in rows 1:3

2. Appending the data from the other sheets at the right of its data would suffice.

 

[Peter_SSs]

Give this a try with a copy of your workbook.

Sub Copy_Sheets()
  Dim i As Long
  
  For i = 2 To Sheets.Count
    Sheets(i).UsedRange.Copy Destination:=Sheets(1).Cells(5, Columns.Count).End(xlToLeft).Offset(-1, 1)
  Next i
End Sub

 

[Ramneek]

Dear Sir,

Worked perfectly. Thanks a lot. You have saved me hell lot of time.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.