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]

It works perfectly as written as long as you are the person opening the second workbook. You can try it and see how it works.

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

I can do this but I will have to leave the macro in the second workbook. What is the full path to the folder containing the Master file? Will the second workbook be saved in the same folder?

 

[anon1999]

It works perfectly as written as long as you are the person opening the second workbook. You can try it and see how it works.

I can do this but I will have to leave the macro in the second workbook. What is the full path to the folder containing the Master file? Will the second workbook be saved in the same folder?

Hi mumps

I haven't been given the Share point folder where this will live in finality yet (only temporary folders so far!) unfortunately.
I've just been working from my Desktop so far for development

 

[mumps]

I will write the code with a generic folder path which you can change when you get the actual path. Will both workbooks be saved in the same folder?

 

[anon1999]

I will write the code with a generic folder path which you can change when you get the actual path. Will both workbooks be saved in the same folder?

Yeah definately

 

[mumps]

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. Change the folder path where indicated to suit your needs. Close the window to return to your sheet. Save the second workbook as a macro-enabled file and then close it. With both workbooks closed, open the second workbook and the macro will run automatically. You can test it by changing the folder path to your Desktop location until you get the actual path.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, srcWS As Worksheet, srcWB As Workbook, DayArr As Variant, i As Long
    DayArr = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
    For Each ws In Sheets(DayArr)
        ws.UsedRange.ClearContents
    Next ws
    Set srcWB = Workbooks.Open("C:\Test\Master.xlsx") 'change the path to suit your needs
    With srcWB.Sheets("MasterData")
        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
    srcWB.Close False
    Application.ScreenUpdating = True
End Sub

 

[anon1999]

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. Change the folder path where indicated to suit your needs. Close the window to return to your sheet. Save the second workbook as a macro-enabled file and then close it. With both workbooks closed, open the second workbook and the macro will run automatically. You can test it by changing the folder path to your Desktop location until you get the actual path.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, srcWS As Worksheet, srcWB As Workbook, DayArr As Variant, i As Long
    DayArr = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
    For Each ws In Sheets(DayArr)
        ws.UsedRange.ClearContents
    Next ws
    Set srcWB = Workbooks.Open("C:\Test\Master.xlsx") 'change the path to suit your needs
    With srcWB.Sheets("MasterData")
        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
    srcWB.Close False
    Application.ScreenUpdating = True
End Sub

Thank you mumps, I will test it when I get in the office tomorrow. Your a star for helping me out whatever though

 

[anon1999]

Morning mumps

When I run this, it brings through the headers of the data, but none of the data itself?

Any idea what I'm missing?

 

[mumps]

Are there any blank rows in your MasterData sheet?

 

[anon1999]

Hi mumps

No there isn't.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your MasterData sheet (de-sensitized if necessary). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.