Morning all,
I really hope you can help me with this. I am proficient with the Excel basics but have never used macros or complex formulae.
I have a workbook with six live worksheets and a final sheet that consolidates all the data from the other six worksheets.
I would like the consolidation sheet to automatically be updated when data from the other sheets is changed or if new data is added.
Is this possible, and if so, how easy is it?
Many thanks in advance.
It should be easy - either formulae in the consolidation sheet to sum data from the first 6 sheets, or a small loop of code to paste+add values from each of the sheets.
Hard to say what is the best approach, without seeing your workbook and required outcome...
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...
Hi Olly,Originally Posted by Olly
Thanks for your response. the spreadsheet is business sensitive so I can't share it, but it holds standard type information such as client names, addresses, various Łamounts in different columns etc.
Each of 6 branches has a separate tab which is in effect a client pipeline. over time the information will be updated and new data added as new clients and/ or prospects are gained.
This information needs to be duplicated on a consolidated sheet for management purposes.
Thanks again
Various data in different columns, huh?
As I said, it's impossible to provide a working solution without seeing your workbook / data structure. But something like this (untested) might be a starting point.
Sub foo() Dim ws As Worksheet With Worksheets("Consolidated") .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Delete End With For Each ws In ThisWorkbook.Worksheets If Not ws.Name = "Consolidated" Then With ws .Range("A2:Z" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy _ Worksheets("Consolidated").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) End With End If Next ws End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks