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

 

[JADownie]

Yes I do have hidden sheets

 

[Fluff]

What do you want to happen to the hidden sheets?

 

[JADownie]

ignore them entirely / leave hidden

 

[Fluff]

In that case use

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