at the moment you have 4 rows in Summary with values. When will you run

the macro? If all 24 rows are filled?

If you run the macro now and later you make new entries and you will run

the macro again, you must test which sheets already exists.

Then you better try:



Sub CopyMainSwb()

Dim arrNames, n As Integer

Dim SheetExists As Boolean



Application.ScreenUpdating = False

With Sheets("Summary")

arrNames = .Range("C9:C32").SpecialCells(xlCellTypeConstants)



For n = LBound(arrNames) To UBound(arrNames)

On Error Resume Next

SheetExists = Not Sheets(arrNames(n, 1)) Is Nothing

If SheetExists = False Then

Sheets("Main Swb").Copy befo=Sheets("NOTES")

ActiveSheet.Name = arrNames(n, 1)

.Range("C9:C32").SpecialCells(xlCellTypeConstants) _

.Cells(n).Offset(, 1).Resize(1, 4) = _

"='" & arrNames(n, 1) & "'!G7"

End If

Next

End With

Application.ScreenUpdating = True

End Sub





Regards

Claus Busch


Excellent point!

Up to now I just had a little macro that would delete the newly made sheets so I could go on about doing test with the codes. That issue was bound to come up once the sheet was in general use instead of testing and developing.

Thanks for the look-ahead.

Howard