Combined all data from 4 sheets to another sheet as consolidated

[tariqnaz2005]

Dear all,

as per attached i want to copy data from 4 sheets to sheet name "all".

Rows of each sheets can be increased or decresed every time.. I want copy all data till the last row and paste in sheet all.

Could you please suggest any vba or other way.

Thanks

[AliGW]

Here is how to do it with PowerQuery: https://www.youtube.com/watch?v=YOC-pEIuHpA

[tariqnaz2005]

Thanks AliGW for your reply, you have a way to learn it. But i am not good in excel. could you or any one else can give me a VBA. thanks.

Actually it is not an amount or numbers to combined..

I want all text or numbers same format from all sheets to be copied and paste in sheet "ALL"

Thanks

[tariqnaz2005]

Hello all,

actually i want to past all date from all 4 sheets, text or numbers any type, and past in sheet "ALL"... pls help me

[alansidman]

As Ali indicated earlier. Power Query is the way to go. Here is a file with all your data and the Mcode that did that.

let
    Source = Table.Combine({aicg, gmtc, ubic, ustc})
in
    Source

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

[tariqnaz2005]

dear alan,

its looks worked, but since i dont have any idea how to run it or how to use mcode.. for me its a simple sheet only. dont know what to do and how to run it again. kindly help to explain

also below error is giving on opening

An error occurred in the ‘aicg’ query. DataSource.Error: Could not find a part of the path 'C:\Users\alans\Downloads\sample file for copy sheets data.xlsx'.
Details:
C:\Users\alans\Downloads\sample file for copy sheets data.xlsx

[alansidman]

You need to change the path to where your file is located. The error you are seeing is because you don't have access to my computer where the file was located when I retrieved the data to bring into Power Query. I used the same technique described in the video Ali presented to you. Follow it using your data file and it should result in the same output. All I did was bring each sheet into PQ and then append each to the other.