Multiple Sheets to One Master Sheet Update

Solved
FFanatics Posts 7 Registration date Tuesday March 28, 2023 Status Member Last seen April 12, 2023 - Mar 28, 2023 at 03:20 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 12, 2023 at 04:09 AM

Hello,

We are a team of 8 who updates a single excel work sheet, and because of this, there is always information missing or overwritten by others etc. I would like to create and lock a master sheet and create 8 separate sheets for every individual so that they can update their relevant fields and I would like this to be updated automatically to the master sheet.

All the rows and columns will be the same just would like the master sheet to update when a user updates their respective sheets automatically.

I am not too good with macros, so if you could please help me step by step, that would be amazing.

For e.g. Master Sheet is called "Consolidated" and individual Sheet will be called, Mr.X, Mr.Y, Ms. Z etc.

Thank you very much for your help.


Windows / Chrome 111.0.0.0

Related:

12 responses

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Mar 28, 2023 at 06:19 AM

Hello FFanatics,

It's a fairly straight forward process to create new sheets for each individual user. However, could you please create an exact copy of your current workbook, upload it to a file sharing site, such as WeTransfer or Drop Box, then post the link to your file back here. If your data is sensitive, then please use dummy data. We only need a few rows of data to work with.

This will give us something to work with and test plus give us a good idea of your data set out so we can understand the overall concept of your intent.

Cheerio,

vcoolio.

0
FFanatics Posts 7 Registration date Tuesday March 28, 2023 Status Member Last seen April 12, 2023
Mar 28, 2023 at 07:43 AM

https://wetransfer.com/downloads/4a196c97f9955b9a58bcda4040b1913420230328113252/bcf62c

Here you go. Thank you for your prompt response and help.

0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Mar 29, 2023 at 08:39 AM

Hello FFanatics,

Thanks for the sample.

I'll have a look at this for you tomorrow.

Cheerio,

vcoolio.

0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Mar 30, 2023 at 07:10 AM

Hello FFanatics,

I've played around with the sample you supplied and I've made some changes to the layout in the hope that your workbook will run smoothly once it is automated.

I've attached your sample with the minor changes shown. I've added a template sheet from which all the User sheets will be created from. This will create uniformity for each User. On the Master sheet, I've added a data validation drop down list in cell A1 which simply lists the User names (for the sake of the exercise, I've only placed three names in the list). Once you click on a User name from the list, a new worksheet will be automatically created for that User. The code for this purpose is:-
 

Private Sub Worksheet_Change(ByVal Target As Range)
      
      Dim wsT As Worksheet: Set wsT = Sheets("Template")
      
      If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
      If Target.Count > 1 Then Exit Sub
      If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False

      If Not Evaluate("ISREF('" & CStr(Target.Text) & "'!A1)") Then
      wsT.Copy After:=Sheets(Sheets.Count)
      ActiveSheet.Name = CStr(Target.Text)
      Range("A1").ClearContents
      End If

Application.Goto Me.[A1]
Application.ScreenUpdating = True

End Sub

If you go into the VB Editor (press Alt + F11), over to the left in the Project Explorer, double click on "Master Sheet". You'll then see the above code in the white code field. This is known as the sheet module.

Next, in Module1, you'll see the following code:-
 

Option Explicit
Sub DataTransfer()

    Dim ws As Worksheet, wsMS As Worksheet
    Set wsMS = Sheets("Master Sheet")

Application.ScreenUpdating = False
    
    wsMS.ListObjects("Table10319").Unlist
    wsMS.[A11].CurrentRegion.Offset(1).Clear
    
    
    For Each ws In Worksheets
          If ws.Name <> "Master Sheet" And ws.Name <> "Template" Then
                ws.[A11].CurrentRegion.Offset(1).Copy
                wsMS.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
          End If
    Next ws
    
    wsMS.ListObjects.Add(xlSrcRange, wsMS.[A11].CurrentRegion.Cells, , xlYes).Name = "Table10319"
    wsMS.ListObjects("Table10319").DataBodyRange.WrapText = False
    wsMS.Columns.AutoFit
    wsMS.Rows.AutoFit
    
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

This code will transfer all the data from the User sheets to the Master sheet. The code is assigned to the "DATA TRANSFER" button on the Master sheet. Hence, once a User has added any data to their sheet, you or the User can go to the Master sheet and click on the button to transfer the latest data. The code will refresh the data from all User sheets.

Here is the link to the sample:-

https://wetransfer.com/downloads/f7d9e9498e1e709646c26a33393b44bc20230330105201/f33fdd

You'll notice in the attached file that there aren't any User sheets just yet. Go to cell A1 and select a User name from the list and you'll notice that a new sheet is created from the Template sheet for the selected User. Go to the newly created sheet and add a few rows of data in all columns then go to the Master sheet and click on the button. The data will appear in the Master sheet. Create more sheets, add or change data then transfer it to the Master sheet by clicking on the button.

You may notice that I tried to refine all the sheets a little just to ensure that all runs smoothly. I've deleted the Excel table formats from the Template and User sheets and only kept the table format for the Master sheet. I don't believe that the User sheets need to have table formats.

I've also added a blank row just above the headings in order to separate the actual data from the clutter that you may have in rows 1 - 9. This is a tidier method and eliminates the risk of any "non - data" interfering with the execution of the codes.

I hope that this helps.

Cheerio,

vcoolio.

0
FFanatics Posts 7 Registration date Tuesday March 28, 2023 Status Member Last seen April 12, 2023
Apr 6, 2023 at 01:45 AM

Hi

the sheet is working like charm. Thank you so much.

I have a question, the date formatting is not coming into the master sheet like it is shown in the user sheet. 
 

in the user sheet if it is 29-Sep-2023 when I press transfer the master sheet shows random numbers like 45198.

could you please help me fix this? Thank you so much.

0

Didn't find the answer you are looking for?

Ask a question
FFanatics Posts 7 Registration date Tuesday March 28, 2023 Status Member Last seen April 12, 2023
Mar 30, 2023 at 08:23 AM

Thank you much, you are a legend. I will have a look at it and let you know how I go. Thank you once again.

0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Apr 6, 2023 at 03:20 AM

Hello FF,

Here's a little background to help you understand:

The number that you see is not a random number, it is a date serial number. The date calendar in Excel starts on the 1st of January, 1900. So, as far as excel is concerned, this particular day starts the beginning of time. In Excel, each day is represented by one whole number. Format any cell as 'Date' then type 1 into the cell. You'll see that the actual date showing will be 1/1/1900. This is the first day of the Excel calendar system. Thus, the number that you see for the 29th of September 2023, is 45198. That's 45,198 days since the "beginning of (Excel) time"! This is the serial number for 29/9/2023.

Now, the reason that you are seeing a serial number instead of an actual date, is this line of code:

wsMS.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues

As you can see in the DataTransfer subroutine, I've elected to paste the data as values only in order to stop your workbook from 'bloating' because, if we remove the PasteSpecial xlValues part, all formatting will also be transferred over to the Master sheet which, over time, will simply increase the size of your workbook and slow it down at the same time.

To overcome seeing the Date Serial numbers instead of dates, you can manually format the date column in the Master sheet to 'Date' or you can add this line of code:-

wsMS.Columns(25).NumberFormat = "dd/mm/yyyy"

directly after Line 21 of the DataTransfer code in post #5  which will automate the process for you. I've assumed that Column 25 (Y) is the date column.

If you are happy to transfer formatting over as well, then these two lines of code:

ws.[A11].CurrentRegion.Offset(1).Copy
wsMS.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues

need to be changed to one line of code as follows:

ws.[A11].CurrentRegion.Offset(1).Copy wsMS.Range("A" & Rows.Count).End(3)(2)

I hope that this helps.

Cheerio,

vcoolio.

0
FFanatics Posts 7 Registration date Tuesday March 28, 2023 Status Member Last seen April 12, 2023
Apr 6, 2023 at 04:00 AM

I inserted

wsMS.Columns(26).NumberFormat = "dd/mm/yyyy”

after

wsMS.ListObjects(“Table10319”).DataBodyRange.WrapText= False

and pressed saved but nothing changed in the master sheet.

0
FFanatics Posts 7 Registration date Tuesday March 28, 2023 Status Member Last seen April 12, 2023
Apr 6, 2023 at 04:02 AM

Also there are multiple date columns like 26 (Z), 27(AA), 28(AB), 29(AC), 31(AE) etc.

please do let me know. Thank you very much.

0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Apr 6, 2023 at 05:42 AM

Hello FF,

I've just tested the proposed changes in the sample that I sent to you via the WeTransfer link and it worked exactly as it should without any issues.

Perhaps just use the single copy/paste line as per my last post:

ws.[A11].CurrentRegion.Offset(1).Copy wsMS.Range("A" & Rows.Count).End(3)(2)

so the code should now look like this:

Option Explicit
Sub DataTransfer()

    Dim ws As Worksheet, wsMS As Worksheet
    Set wsMS = Sheets("Master Sheet")

Application.ScreenUpdating = False
    
    wsMS.ListObjects("Table10319").Unlist
    wsMS.[A11].CurrentRegion.Offset(1).Clear
    
    
    For Each ws In Worksheets
          If ws.Name <> "Master Sheet" And ws.Name <> "Template" Then
                ws.[A11].CurrentRegion.Offset(1).Copy wsMS.Range("A" & Rows.Count).End(3)(2)
          End If
    Next ws
    
    wsMS.ListObjects.Add(xlSrcRange, wsMS.[A11].CurrentRegion.Cells, , xlYes).Name = "Table10319"
    wsMS.ListObjects("Table10319").DataBodyRange.WrapText = False
    wsMS.Columns.AutoFit
    wsMS.Rows.AutoFit
    
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Remove the previous code and replace it with the above code, save and close the workbook then re-open it to test.

Cheerio,

vcoolio.

0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Apr 6, 2023 at 05:47 AM

BTW FF,

In relation to your post #9, you need to save the change then run the code again.

Cheerio,

vcoolio.

0
FFanatics Posts 7 Registration date Tuesday March 28, 2023 Status Member Last seen April 12, 2023
Apr 12, 2023 at 02:24 AM

It’s perfect. Sometimes I get an error when I protect the sheet or play around with the data or tabs. So I have use the previously saved version. But other than that. It is working exactly how I wanted it to. Thank you so much for all your help.

0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Apr 12, 2023 at 04:09 AM

You're welcome FF. I'm happy to have been able to assist.

Cheerio,

vcoolio.

0