VBA- updates all sheets into one master sheet

99Ahmad99

New Member
Joined
Dec 19, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I have one excel workbook, containing (30) sheets. all sheets with the same setup, same arrangements, same headers and same titles.
I need a micro, once there are any updates, in any of those (30) sheets, I need to copy those updates and put them in the master sheet which is within the same workbook.
Thanks.
 
If you use the Power Query solution, the every time you change one of the 30 sheets and click on Refresh All on the Data Tab, it will update the Master. There is no need to delete and rebuild in Power Query.
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Thanks for the reply.
what about this idea, what about keeping those 30 sheets as they are, and the update will be on a master sheet?
this master sheet will always be empty, and once I run the micro, each sheet will copy its contents to the master one? every time I run the mico, the master sheet will erase its previous contents and copy from those 30 sheets so it will be always updated? how is that sound?
any help please
 
Upvote 0
If you use the Power Query solution, the every time you change one of the 30 sheets and click on Refresh All on the Data Tab, it will update the Master. There is no need to delete and rebuild in Power Query.
you are right, but in order to keep the sheets the way they look, formatted, the only option I think is to remove and build them again using erow().
what do you think?
 
Upvote 0
any help please
You might consider the following...

VBA Code:
Sub SubmittalsSummary()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow1 As Long, LastRow2 As Long

Set ws1 = ThisWorkbook.Worksheets("Schedule of Submittals")
LastRow1 = ws1.Cells(Rows.Count, "C").End(xlUp).Row
If LastRow1 < 6 Then LastRow1 = 6
ws1.Range("C6:AH" & LastRow1).Clear

For Each ws2 In ThisWorkbook.Worksheets
    If ws2.Name <> "DASHBOARD" And ws2.Name <> "Schedule of Submittals" Then
        LastRow1 = ws1.Cells(Rows.Count, "C").End(xlUp).Row + 1
        LastRow2 = ws2.Cells(Rows.Count, "C").End(xlUp).Row
        ws2.Range("C6:AH" & LastRow2).Copy Destination:=ws1.Range("C" & LastRow1)
    End If
Next ws2
End Sub

Happy Holidaze!

Tony
 
Upvote 0
You're very welcome. If the macro does what you asked for, please mark your question as solved. Thanks.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,583
Messages
6,114,482
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top