Guys,
I almost have what i'm looking for, but not quite there, and the final hurdle is that existing VBA code below copies all of the worksheet data required, but does not copy formats or page breaks which kind of defeats the object of what i'm trying to achieve if i have to manually set formats for 100 worksheets individually.
I have discovered some VBA code that does seem to do a direct copy and preserve formats and pagebreaks, but my limited VBA knowledge prevents me from merging the 2 to produce a result.
Existing Code
Sub dingbat4()
Dim ws As Worksheet 'source sheet'
Dim ws2 As Worksheet 'target sheet'
Dim i As Long
Application.ScreenUpdating = False
Set ws = Sheets("Template") 'source worksheet'
For i = 101 To 200 'cycle thru to insert 100 worksheets'
Sheets.Add.Name = i 'add sheet name starting 101 ending 200'
Set ws2 = ActiveSheet 'inserted sheets'
ws.UsedRange.Copy ws2.Range("A1") 'copy used range to start cell "A1" new sheet'
ws2.Range("e9").Value = ws2.Name 'insert sheet name in cell "e9" on each sheet'
ws2.Move After:=Sheets(Sheets.Count) 'sheets numbering starts at 101'
Set ws2 = Nothing
Next i
Application.ScreenUpdating = True
End Sub
Code that copies sheet and preserves formatting
Sub Copier()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Sheet1").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
End Sub
Thanks in anticipation
Dingbat
(PS - Thanks to John H Davis for getting me this far...)
Bookmarks