• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy multiple worksheets with cell reference to to worksheets


New Member

I want to Copy 3 worksheets i.e. A, B & C multiple time (count defined in "Summary" worksheet cell H6).
In Original B has formula that takes data from A and similarly C has formula that takes data from A.
Similarly when the B_New file is copied it should take data from A_New but no A.

Please check the macro I am using a suggest correction.
Sub CopyWSs()
Dim ws As Worksheet
Dim n As Integer
'Dim ws1 As Worksheet
On Error Resume Next
    n = InputBox("How many copies of the active sheet do you want to make?")
    If n >= 1 Then
For Each ws In ActiveWorkbook.Sheets
For numtimes = 1 To n
    Select Case ws.Name
        Case "Summary" ' add any other sheets not to copy here
            ' do nothing
        Case Else
            'ws.Copy After:=ActiveWorkbook.Sheets("Test Planning")
             ws.Copy After:=Worksheets(Sheets.Count)
            ActiveSheet.Name = ws.Name & " New"
    End Select
Next ws
End If
End Sub
Last edited by a moderator: