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.
Printable View
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?Quote:
Originally Posted by simora
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
Will select up to 3 sheets to the right of the active sheet. Assumes no hidden sheets exist among those being selected.Code: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
If there are hidden sheets then this will select up to 2 visible sheets to the right of the active sheet:
edit: didn't read the "and copy them to the end of the visible worksheets using VBA code", so:Code: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
ps. Excel 2007 extension is xlsx/xlsm/xlsbCode: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
Quote:
Originally Posted by Paul_Hossler
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 )
p45cal :
Thanks.
( Somehow, my replies were NOT showing up. Not sure what's going on)
In my experience that's true unti it's notQuote:
Originally Posted by simora
I try to use defensive programming just in case, but that's just me
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?Quote:
Originally Posted by simora
Paul_Hossler
Agree.
Good programming practice.
Thanks