Hi
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.
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.
Code:
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
Next ws
End If
End Sub
Last edited by a moderator: