I have a VBA script that creates multiple worksheets from a masterdata sheet. (File attached/code below). I would like the data to be created on a templated worksheet rather than a plain one.
Can this be done?
In my example the MasterData worksheet has all my reporting, the macro reads column A and creates a new sheet for each name change. You can run the code to see an example.
Also included in the workbook is 'Example with tempate' this is how I want each created sheet to look with the relevant data. Rows 1-10 contain data I'd like on each report. I would also like the managers name to appear in Cell C5 rather than as the first column.
Can you help?
Dim ws As Worksheet, sh As Worksheet, rng As Range, cel As Range
Application.ScreenUpdating = False
Set ws = Worksheets("MasterData")
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rng = .SpecialCells(xlVisible)
For Each cel In rng.Cells
If Trim(cel.Value) <> "" And cel.Row <> rng.Cells(1).Row Then
Set sh = ActiveSheet
sh.UsedRange.AutoFilter Field:=sh.Columns("A").Column, Criteria1:="<>" & cel, Operator:=xlAnd
sh.Name = cel
Application.ScreenUpdating = True