Previous Topic Next Topic

Master sheet to auto populate from multiple sheets

Closed
Mukundi_001 Posts 2 Registration date Thursday July 14, 2022 Status Member Last seen July 18, 2022 - Jul 15, 2022 at 03:41 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jul 19, 2022 at 04:34 AM

hello!

could someone kindly assist me with a formula or a macro that will auto populate/update the master sheet  using information from sheets A-E. this is basically recovery of monies from clients where we undercharged for work done. the sheets include the job number to be recovered, when it was recovered and how much was recovered. the "feeder sheets are in various states of completion or are partly populated as we may not have all the information until the client orders again.

please keep in mind that the actual documentation consists of 60 sheets that will need to feed into the master sheet

i intend on sharing this document with 4 of my colleges so we can simultaneously keep track of recoveries

a sample of the document as been provided below

any help would be greatly appreciated

thanks-a-mil!

https://onedrive.live.com/edit.aspx?resid=D2AFC1B0CE3E121B%21922&ithint=file%2Cxlsx&authkey=%21AmvuxPMweTR4J6A

Related:

3 responses

Answer 1 / 3
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jul 16, 2022 at 12:24 AM

Hello Mukundi,

Try the following code assigned to a button:-

  1. Sub Test()
  2.     Dim ws As Worksheet, wsM As Worksheet
  3.     Set wsM = Sheets("Master ")
  4.     Application.ScreenUpdating = False
  5.     wsM.[A3].CurrentRegion.Offset(1).Clear
  6.     For Each ws In Worksheets
  7.             If ws.Name <> "Master " Then
  8.                   ws.[A3].CurrentRegion.Offset(1).Copy wsM.Range("A" & Rows.Count).End(3)(2)
  9.             End If
  10.     Next ws
  11.     Application.ScreenUpdating = True
  12.     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.
0
Answer 2 / 3
Mukundi_001 Posts 2 Registration date Thursday July 14, 2022 Status Member Last seen July 18, 2022
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
0
Answer 3 / 3
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
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.
0