Automating exporting multiple sheets to new files

[hmopheim]

Hi guys

(Question is in regard to Excel for Mac 365)

I have a fairly massive workbook consisting of around 40-50 sheets. Three of the sheets contains common variables / master data and some calculations, and values from these three are then used in separate sheets per department. In order to uphold security protocols, I need to export each department sheet, together with the three common variables sheets, into a separate file named after the department sheet, which is then shared via Onedrive to each department head.

So typically, the workbook consist of the following sheets;
Variables1
Variables2
Variables3
Department1
Department2
[...]
Department30

The goal is to get a macro or some other way to export this into 30 new and separate Excel files, stored in a specific file path on a Onedrive setup. Where each file would consist of the following sheets;
Variables1
Variables2
Variables3
DepartmentX (where X is each individual department name)

The files should be named "DepartmentX.xlsx" (again, X representing each department), based on the sheet name of each department. This *could* be hard-coded, as the department names, sheetnames for the Variables sheets and filepath will not change.

Is there a way to create a Macro to accomplish this with a "one-click-solution", as this is a repetitive task? I'm definitely not a VBA wizard, I have messed around a bit based on some tips I found, but so far not been successful.

Thanks!

 

[Herakles]

Have you tried using the macro recoder?

 

[hmopheim]

Have you tried using the macro recoder?

Yep. I may be a numpty, but from what I can gather looking at the code it generates, it doesn't seem to register the actual creation of a new workbook after the copying action, and also seems to not work well going back and forth between the original document and the newly created ones between copying sheets, creating a new file (from the move&copy action from right-clicking one of the marked sheets), saving that, going back and doing it again with a new department file.

 

[Herakles]

Create the new workbook and then copy the sheets.

You can select all of the sheets to copy and then copy them.

 

[hmopheim]

Ok, I've been fiddling around with VBA again, and I thought I almost had it, but now it has stopped working. It freezes at some point during export, and I have to force quit Excel. And I can't seem to figure out where I'm going wrong. Any VBA wizards able to spot obvious errors or violations on best practice here? Hopefully the code is self-explanatory.

Sub ExportWorksheets()
    
    'Set variables
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ThisWorkbook
    
    'Export 1
    Dim wb1 As Workbook
    Set wb1 = Workbooks.Add
    
        'Select correct worksheets
        For Each ws In wb.Worksheets
            If ws.Name = "Variables1" Or ws.Name = "Variables2" Or ws.Name = "Variables3" Or ws.Name = "Department 1" Then
                ws.Copy After:=wb1.Sheets(wb1.Sheets.Count)
            End If
        Next ws
    
        'Delete default sheet if present in new file
        Application.DisplayAlerts = False
        For Each ws In wb1.Worksheets
            If ws.Name = "Sheet1" Then
                ws.Delete
            End If
        Next ws
    
        'Remove links to original document if present
        For Each ws In wb1.Worksheets
            For Each cell In ws.UsedRange.Cells
                If InStr(1, cell.Formula, "[Department file Macro Test.xlsm]", vbTextCompare) > 0 Then
                    cell.Formula = Replace(cell.Formula, "[Department file Macro Test.xlsm]", "")
                End If
            Next cell
        Next ws
    
        'Protect all sheets
        For Each ws In wb1.Worksheets
            ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Next ws
        Application.DisplayAlerts = True
    
        'Save and close
        wb1.SaveAs FileName:="Department1.xlsx", FileFormat:=51
        wb1.Close

End Sub

 

[hmopheim]

Ok, some testing later it seems that the culprit is the section I made to replace external links, this might be due to large data amounts I guess. Is there another way to avoid this issue? The problem is that formulas in the Department1 sheet that pulls values from the Variables sheets, will be changed to external links pointing back to the original file in the exported file. And the users for the exported file don't have access to the original file. Links in the exported files must be "identical" in the sense that they must pull data from the "local" Variables sheets - this is why I'm including them in the export in the first place...