Changing worksheet order w/ VBA

[Bill McKeever]

I'm having trouble writing VBA code to manipulate Excel
workbook objects. For example, I want to rearrange
worksheet order. Why does the code in the first example
(below) work, whereas, the code in the second example
does not?

Sub Move_After10thSheet()

'This code works:

Sheets("Jun-12").Select
Sheets("Jun-12").Move After:=Sheets(10)
End Sub

-------------------------------------------------------

Sub Move_AfterTenthSheet()

'This code does not work:

Dim myString As String

arg = "Sheets(10)"

Sheets("Jun-12").Select
Sheets("Jun-12").Move After:=myString
End Sub

 

[jaf]

Hi Bill,
Your code never defines myString and therefore it = "".

 

[Bill McKeever]

Oops. Typo in my previous post. John's reply is
correct. However, his proposed fix doesn't solve the
problem. The puzzle remains. The previous post should
have said:

I'm having trouble writing VBA code to manipulate Excel
workbook objects. For example, I want to rearrange
worksheet order. Why does the code in the first example
(below) work, whereas, the code in the second example
does not?

Sub Move_After10thSheet()

'This code works:

Sheets("Jun-12").Select
Sheets("Jun-12").Move After:=Sheets(10)
End Sub

--------------------------------------------

Sub Move_AfterTenthSheet()

'This code does not work:

Dim myString As String

myString = "Sheets(10)"

Sheets("Jun-12").Select
Sheets("Jun-12").Move After:=myString
End Sub

 

[Malcolm Smith]

Willy

The first code sample works because Sheets(10) is a object of type
WorkSheet. It is not a string at all.

- Malc
www.dragondrop.com

 

[jaf]

Hi Bill,
Try...
Sheets("Jun-12").Move After:=sheets(myString)

 

[Bill McKeever]

Thanks, John. That works, provided myString is
dimensioned as an integer.
Myopia. That's my excuse. Anyway, thanks again.

Bill

 

[jaf]

Hi Bill,
Keep in mind sheets is a collection. So sheets(1) is not necessarily
"sheet1".
Sheets is in tab order left to right. So sheets(5) is the always fifth sheet
from the left.
In an new workbook, if you move "sheet7" between "sheet3" & "sheet4" then
sheets(5)="sheet4".