Hi all
I have a macro on a file that copies data and creates separate tabs from "master" using "template" as the basis.
Sub CreateSheets()
Dim ms As Worksheet
Dim ws As Worksheet
Dim sh As Worksheet
Dim cell As Range
Dim rng As Range
Dim oRange As Range
Application.ScreenUpdating = False
Set ms = ThisWorkbook.Sheets("Master")
Set ws = ThisWorkbook.Sheets("Template")
Set rng = ms.Range("A5:A" & ms.Cells(Rows.Count, 1).End(xlUp).Row)
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> ms.Name And sh.Name <> "Template" Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
For Each cell In rng
If cell.Value = "" Then
If oRange Is Nothing Then Set oRange = cell Else Set oRange = Union(oRange, cell)
End If
Next cell
If Not oRange Is Nothing Then oRange.ClearContents
For Each cell In rng.SpecialCells(2).Areas
ws.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
ActiveSheet.Name = ms.Range(Split(cell.Columns(3).Offset(1).Address, ":")(1)).Value
cell.Resize(cell.Rows.Count + 1, cell.Columns.Count + 9).Copy
ActiveSheet.Range("A5").PasteSpecial xlPasteAll
Application.Goto ActiveSheet.Range("A1")
Next cell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
it seems to be working fine, however, it messes on this line:
ActiveSheet.Name = ms.Range(Split(cell.Columns(3).Offset(1).Address, ":")(1)).Value
it seems to create all tabs from master except for row 74 --> could it be bc column C value is listed only once?
thxs
Bookmarks