Splitting a master data set into a different workbook

[anon1999]

Hi all

I have a need to split a master data set into different tabs on a separate workbook.

This has come about because I can't let the end user have access to the Master set as they have deleted it several times!

Essentially, this is a very small sample of my master data set:

ID Company Day of Service
1 Fake Ltd Monday
2 Stark Industries Tuesday
3 Black Mesa Wednesday
4 Delos inc Thursday
5 E Corp Friday

I am wondering if there is a way to have a second workbook, which, when opened, pulls across the data from my master sheet, and splits into into tabs based on the day of the week.

Both workbooks would be held in a Sharepoint library, but the end user would only have access to the workbook that is split by day (so they can't delete the master!)

This is my intended result:

Where in this example, Monday would contain:
ID Company Day of Service
1 Fake Ltd Monday

Tuesday would contain:
ID Company Day of Service
2 Stark Industries Tuesday

and so on....

I am aware of some VBA which allows splitting, but have not been able to make them work when substituting in references to other workbooks.

I hope that makes sense.

Thank you

 

[mumps]

Do you want the second workbook to be updated automatically when you open it? If so, the data in the master will be copied each time you open it which may cause the same data to be copied multiple times. Please clarify.

 

[anon1999]

Hi mumps

My intention was yes that second workbook to be updated automatically, I hadn't considered the possible duplication of data, although presumably a piece of VBA could be added to the Workbook Close module which clears the sheets and deletes the tabs every time it is closed?

 

[mumps]

What is the name of the sheet containing the data in the master workbook? What is the full name of the master workbook including the extension (xlsx, xlsm)? Will the day sheets (Monday to Friday) already exist in the second workbook?

 

[anon1999]

Hi mumps

The Master workbook is simply called Master.xlsx, and the sheet is called 'MasterData'.
I'm completely open to suggestions on the second workbook, in terms of the sheets could be there, or not be there, depending on what the potential solution would be.

 

[mumps]

It would be easier if the the sheets already existed in the second workbook. Will there always be just 5 sheets, Monday to Friday?

 

[anon1999]

Hi mumps

Yes, Monday - Friday.

 

[mumps]

Make sure that the master workbook is open and that the second workbook has the 5 day sheets using the full day names (not abbreviations). Place the macro below in the second workbook code module for ThisWorkbook. Do the following: Select any sheet in the second workbook to make it the active sheet. Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Save the second workbook as a macro-enabled file. Then close it. With the master workbook already open, now open the second workbook. The data should be automatically copied to the day sheets.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, srcWS As Worksheet, DayArr As Variant, i As Long
    Set srcWS = Workbooks("Master.xlsx").Sheets("MasterData")
    DayArr = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
    For Each ws In Sheets(DayArr)
        ws.UsedRange.ClearContents
    Next ws
    With srcWS
        For i = LBound(DayArr) To UBound(DayArr)
            .Cells(1, 1).CurrentRegion.AutoFilter 3, DayArr(i)
            .AutoFilter.Range.Copy Sheets(DayArr(i)).Cells(Sheets(DayArr(i)).Rows.Count, "A").End(xlUp).Offset(1)
            Sheets(DayArr(i)).Rows(1).Delete
        Next i
    End With
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[mumps]

I just realized that this approach has to be changed because the macro will run automatically when the second file is opened. So every time the end user opens it, an error will be generated if the master is not open. I will have to place the macro in the Master instead of the second workbook. However, you will have to run the macro manually instead of it triggering automatically. Give me a few minutes to make the change and I will get back to you.

 

[anon1999]

I was going to say it looks amazing.

Is there a way for it to run whilst the Master workbook is NOT open by any chance?

Thank you!!