VBA Code to select specific sheet from the ActiveSheet

[simora]

In Excel 2007, so an xls file, How to select the ActiveSheet plus the two sheets on the right, ( 3 Sheets ) and copy them to the end of the visible worksheets using VBA code.
Cant Hardcode the Sheet Names because they change.

[Paul_Hossler]

In Excel 2007, so an xls file, How to select the ActiveSheet plus the two sheets on the right, ( 3 Sheets ) and copy them to the end of the visible worksheets using VBA code.
Cant Hardcode the Sheet Names because they change.

Are the visible worksheets in the ActiveWorkbook with the ActiveSheet?

What happens if the ActiveSheet is so far to the right that it doesn't have 2 more sheets?

So ...

Sheet20
Sheet15
Sheet10 <<< ActiveWorksheet
Sheet5
Sheet6 <<<<< Hidden
Sheet7
Sheet8

Ends up like

Sheet20
Sheet15
Sheet6 <<< hidden
Sheet8
Sheet10 <<< ActiveWorksheet
Sheet5
Sheet7

[p45cal]

Sub blah()
    a = ActiveSheet.Index
    b = Application.Min(Sheets.Count, a + 2)
    For i = a + 1 To b
        Sheets(i).Select False
    Next i
End Sub

Will select up to 3 sheets to the right of the active sheet. Assumes no hidden sheets exist among those being selected.

If there are hidden sheets then this will select up to 2 visible sheets to the right of the active sheet:

Sub blah2()
    i = ActiveSheet.Index
    Do Until Z = 2 Or i = Sheets.Count
        i = i + 1
        If Sheets(i).Visible Then
            Z = Z + 1
            Sheets(i).Select False
        End If
    Loop
End Sub

edit: didn't read the "and copy them to the end of the visible worksheets using VBA code", so:

Sub blah3()
    NoSTM = 3   'No. of Sheets To Move
    ReDim mySheets(1 To NoSTM)
    i = ActiveSheet.Index
    Z = 1
    Set mySheets(Z) = ActiveSheet
    Do Until Z = NoSTM Or i = Sheets.Count
        i = i + 1
        If Sheets(i).Visible Then
            Z = Z + 1
            Set mySheets(Z) = Sheets(i)
        End If
    Loop
    For j = 1 To Z
        mySheets(j).Move After:=Sheets(Sheets.Count)
    Next j
    mySheets(1).Activate
End Sub

ps. Excel 2007 extension is xlsx/xlsm/xlsb

[simora]

Are the visible worksheets in the ActiveWorkbook with the ActiveSheet?

What happens if the ActiveSheet is so far to the right that it doesn't have 2 more sheets?

So ...

Sheet20
Sheet15
Sheet10 <<< ActiveWorksheet
Sheet5
Sheet6 <<<<< Hidden
Sheet7
Sheet8

Ends up like

Sheet20
Sheet15
Sheet6 <<< hidden
Sheet8
Sheet10 <<< ActiveWorksheet
Sheet5
Sheet7

[simora]

Paul_Hossler : There will ALWAYS be 3 or more visible sheets to the right of the Active Sheet.

( Somehow, my replies were NOT showing up. Strange )

[simora]

p45cal :
Thanks.

( Somehow, my replies were NOT showing up. Not sure what's going on)

[Paul_Hossler]

Paul_Hossler : There will ALWAYS be 3 or more visible sheets to the right of the Active Sheet.

In my experience that's true unti it's not

I try to use defensive programming just in case, but that's just me

[p45cal]

p45cal :
Thanks.

( Somehow, my replies were NOT showing up. Not sure what's going on)

I've just realised that I may still have misread the question: you want the sheets copied to the end, not moved to the end?

[simora]

Paul_Hossler


Agree.
Good programming practice.
Thanks