Macro Looping Help

[JADownie]

I have this macro below that works fine when I only need to copy 2 sheets, but lets say now I have 10? How would I tweak my code below for that?


Sub CopyToNew()

Dim NmStr As String
NmStr = ActiveWorkbook.Name

'Select Next Sheet

activesheet.next.select


'Copy the active sheet to a new Workbook.
ActiveSheet.Copy

Windows(NmStr).Activate


'Select Next Sheet

activesheet.next.select


'Copy the active sheet to a new Workbook.
ActiveSheet.Copy

Windows(NmStr).Activate




End Sub

 

[Fluff]

Are you trying to copy all sheets to there own workbook, or just some?

 

[JADownie]

Are you trying to copy all sheets to there own workbook, or just some?

So part 1 of this macro creates individual sheets based on the cell in a column value on a master sheet (could be 2, could be 10?) Then I want this macro to cycle through each of those new sheets and copy them into a new workbook.

 

[Fluff]

Which is the first sheet that needs to be copied?

 

[JADownie]

Whatever the next active sheet is in the workbook, name will vary each time

 

[Fluff]

Ok, how about

Sub JADownie()
   Dim i As Long
   Application.ScreenUpdating = False
   With ActiveWorkbook
      For i = ActiveSheet.Index + 1 To .Sheets.Count
         .Sheets(i).Copy
      Next i
   End With
End Sub

 

[JADownie]

Ok, how about

Sub JADownie()
   Dim i As Long
   Application.ScreenUpdating = False
   With ActiveWorkbook
      For i = ActiveSheet.Index + 1 To .Sheets.Count
         .Sheets(i).Copy
      Next i
   End With
End Sub

This works perfect when I have just this code in a new blank workbook. But when I add the code to my existing workbook I get an error at this line

.Sheets(i).Copy


Makes no sense now?

 

[Fluff]

What error do you get?

 

[JADownie]

Runtime Error '1004'
We couldn't copy this sheet

with no Debug option

 

[Fluff]

Do you have any hidden or very hidden sheets?