Multiple Sheets to One Master Sheet Update

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.

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

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

Hello FFanatics,

Thanks for the sample.

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

Cheerio,

vcoolio.

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.

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.

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.

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.

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.

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.

BTW FF,

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

Cheerio,

vcoolio.

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.

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

Cheerio,

vcoolio.