Selecting Worksheets from active to end

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Hi,

getting stuck on how to select a group of sheets from the active sheet to
the end.

I know activesheet.select picks the current sheet
and Sheets(Sheets.Count).Select picks the last sheet

but what code will select from the active sheet to the last sheet including
all those in between...reason is I want to take that group and move it to a
new book

I saw that Sheets(Array("worksheet 5","worksheet 6", "worksheet7")).select
will pick sheets but my sheet names are constantly changing so I need
variables...

I thought if the activesheet was WSN and the last sheet was called WSL then
Sheets(Array(WSN:WSL)).select would work but I get type mismatch error.

thanks for any assistance.
 
This should get you started

Option Explicit

Sub FindActiveSheetToEnd()

Dim WS As Excel.Worksheet
Dim aWS As Excel.Worksheet

Set aWS = ActiveSheet

For Each WS In ThisWorkbook.Worksheets
If WS.Index > aWS.Index Then
'Move worksheet
End If
Next WS

End Sub

HTH, Barb Reinhardt
 
Assuming you want by index then this should get you started. Suggest one at
a time.

Sub ActiveSheetToEnd()
For i = ActiveSheet.Index To Sheets.Count
MsgBox Sheets(i).Name
'Sheets(i).copy
Next i
End Sub
 
Back
Top