Hello all,
I want to copy data from sheet 1 to sheet 2 below the last used cell/row.
Attaching excel file for better understanding, I want the highlighted cells to go to sheet2 with M or VBA code. or if any other easy alternatives available.
Hello all,
I want to copy data from sheet 1 to sheet 2 below the last used cell/row.
Attaching excel file for better understanding, I want the highlighted cells to go to sheet2 with M or VBA code. or if any other easy alternatives available.
Using VBA
Sub TransferData() Sheet1.Range("A2", Cells(Rows.Count, 2).End(xlUp)).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) End Sub
Using Power Query (M)
Format data in Sheet1 as a table (TableNew).
Format Data in Sheet2 as a table (TableTemp)
Create query:
Name the query TableCurrent, and Load To Table on Sheet2.let CurrentData = Excel.CurrentWorkbook(){[Name="TableTemp"]}[Content], NewData = Excel.CurrentWorkbook(){[Name="TableNew"]}[Content], Output = Table.Combine({CurrentData,NewData}), #"Changed Type" = Table.TransformColumnTypes(Output,{{"Date", type date}, {"Count", Int64.Type}}) in #"Changed Type"
Delete TableTemp, and amend query to:
let CurrentData = Excel.CurrentWorkbook(){[Name="TableCurrent"]}[Content], NewData = Excel.CurrentWorkbook(){[Name="TableNew"]}[Content], Output = Table.Combine({CurrentData,NewData}), #"Changed Type" = Table.TransformColumnTypes(Output,{{"Date", type date}, {"Count", Int64.Type}}) in #"Changed Type"
Now whenever you refresh data, whatever is in the table on Sheet1 will be appended to the table on Sheet2.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks