Split Workwook and save

[mojogonzalez]

Hello there!

I have a complex issue (for my standards), that I would like to resolve using VBA in Excel.

I am a total VBA noob, only used it for simple macros so far, using internet sources.

There are three worksheets in my workbook. (Data, Pivot and Dashboard)

"Data" contains the data that the other worksheets are based on.

“Pivot” contains Pivot tables based on "Data".

“Dashboard” contains visualisations that change, depending on the item selected in cell C4. (the visualisations are based on the Pivot tables)

I want to split the workbook in different workbooks based on the team (column A), so there should be 5 files in total (A, B, C, D, E). They should be encrypted with the passwords in column B.

I found a code online doing exactly that:
_____________________________

Sub split()


Dim MyDic As Object, rng As Range, Zelle As Range, ws As Worksheet, wb As Workbook
Application.ScreenUpdating = False
Set MyDic = CreateObject("Scripting.Dictionary")
Set ws = ActiveSheet
With ws
   Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
   For Each Zelle In rng.Offset(1, 0)
       If MyDic(Zelle.Value) = "" And Not IsEmpty(Zelle) Then
           MyDic(Zelle.Value) = 1
           rng.AutoFilter field:=1, Criteria1:=Zelle
           Set wb = Workbooks.Add
           .UsedRange.SpecialCells(xlCellTypeVisible).Copy wb.Sheets(1).Cells(1, 1)
           wb.SaveAs Filename:=ThisWorkbook.Path & "" & Zelle & ".xlsx", FileFormat:=51, Password:=Cells(2, 2)
           wb.Close False
           rng.AutoFilter
       End If
   Next
End With
Application.ScreenUpdating = True
End Sub

______________________________

I implemented the “split” button in the data worksheet.

Now the thing is, it only saves the respective rows from the "Data" worksheet", but the saved files should contain all three worksheets (Data, Pivot and Dashboard).

Is it possible to do that using VBA?

In the final files, each team should see the goals and hours played of their own team, but not the other teams.

I also attached the excel file.

And I apologize for my bad English.

Thank you for your time and effort!

Best Regards


mojo-G

PS. I use Microsoft office 365 / Excel 2302