Master sheet to auto populate from multiple sheets
Closedvcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jul 19, 2022 at 04:34 AM
- Master sheet to auto populate from multiple sheets
- Master royale - Download - Strategy
- Google sheet right to left - Guide
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- How to stop facebook from auto refreshing - Guide
- Windows network commands cheat sheet - Guide
3 responses
Jul 16, 2022 at 12:24 AM
Hello Mukundi,
Try the following code assigned to a button:-
- Sub Test()
- Dim ws As Worksheet, wsM As Worksheet
- Set wsM = Sheets("Master ")
- Application.ScreenUpdating = False
- wsM.[A3].CurrentRegion.Offset(1).Clear
- For Each ws In Worksheets
- If ws.Name <> "Master " Then
- ws.[A3].CurrentRegion.Offset(1).Copy wsM.Range("A" & Rows.Count).End(3)(2)
- End If
- Next ws
- Application.ScreenUpdating = True
- End Sub
It should do the task for you.
With each worksheet, please ensure that row2 is totally blank. Most sheets in your sample show row2 as blank but there was one that didn't. Not having row2 as blank will cause the code to error.
I hope that this helps.
Cheerio,
vcoolio.
Jul 18, 2022 at 02:45 AM
hi vcoolio
thanks for your help thus far
i have tried to appy this code however when i populate sheet "A" with new data it does not auto populate the master sheet
please see below in the workbook i"ve shared
https://onedrive.live.com/redir?resid=D2AFC1B0CE3E121B!928&authkey=!AAV85Ziake_WYBk&ithint=file%2cxlsx&e=kg8Cox
Jul 19, 2022 at 04:34 AM
Hello Mukundi,
You've changed the set out of your data sets in the source sheets. This is why you are having the problem. Your CurrentRegion now starts in row4 whereas in your previous sample it started in row3. The code amended as follows should sort the problem out for you:-
Sub Test() Dim ws As Worksheet, wsM As Worksheet Set wsM = Sheets("Master ") Application.ScreenUpdating = False wsM.UsedRange.Offset(2).Clear For Each ws In Worksheets If ws.Name <> "Master " Then ws.[A4].CurrentRegion.Offset(1).Copy wsM.Range("A" & Rows.Count).End(3)(2) End If Next ws Application.ScreenUpdating = True End Sub
As said in my previous post, make sure that all your source sheets are set out exactly the same. This is a standard Excel protocol. In your recent sample Sheet D is set out differently from the other sheets. Change it to match the others with the headings in row4 otherwise you will receive more error messages.
I hope that this helps.
Cheerio,
vcoolio.