VBA Help needed

[vik2slick]

Hi,

I am trying to sort/group my excel worksheets based on a customized array.

I currently used this below VBA that I found on this site, but it didn't work. The range is in cells A206:A340 in a sheet named 'Cost Center Summary'

VBA used:

Sub SortWS2()
Dim SortOrder As Variant
Dim Ndx As Long
With Worksheets("Cost Center Summary").Range("A206:A340")
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
Next Ndx
End With
End Sub


Please help as this VBA didn't work.

 

[StephenCrump]

You haven't told us what "didn't work" means?

If A206:A340 contains a list of worksheet names, then the macro should shuffle the worksheets in this order to the start of the workbook.

But one possible way the code won't work is if your sheet names are numbers, e.g. my default sheet names are 1, 2, 3 etc ...

If so, this code will need adjusting, because there is a difference between Worksheets(1) and Worksheets("1").

 

[Caleeco]

At a guess... I think this is what you're after:

Sub SortWS2()
'// Move worksheets to align with order shown in `Cost Center Summary` Sheet
    Dim i As Long
    Dim wsCost As Worksheet
    
    Set wsCost = Worksheets("Cost Center Summary")
    For i = 340 To 206 Step -1
        Worksheets(wsCost.Range("A" & i).Value).Move before:=ActiveWorkbook.Sheets(1)
    Next i
End Sub

Let me know if thats what you wanted
Thanks
Caleeco

 

[vik2slick]

Stephen,

Yes, my worksheets are 6 digit numbers and so when I ran the above macro, it didn't seem to "work" meaning the workbook didn't get sorted but instead came back with an error message. Since the worksheets are #'s, will Worksheets("1") do the trick?

 

[vik2slick]

Caleeco,

When I used your VBA script, I received Run-time error '9': Subscript out of range

 

[Caleeco]

Hello,

On which line?
What is the sheet name with the list of other sheet names? is it "Cost Center Summary"?

Thanks
Caleeco

 

[StephenCrump]

Yes, my worksheets are 6 digit numbers .....

Yes, the problem looks like your numeric sheet names ...

Worksheets(123456) refers to the 123,456th worksheet, which will cause an Error 9, subscript out of range error.

You actually want Worksheets("123456").

Try changing:

Worksheets(CStr(.Cells(Ndx).Value)).Move before:=Worksheets(1)

 

[vik2slick]

hello,

on which line?
What is the sheet name with the list of other sheet names? Is it "cost center summary"?

Thanks
caleeco

yes

 

[vik2slick]

Stephen,

Still received Run-time error '9': Subscript out of range

 

[StephenCrump]

Stephen,

Still received Run-time error '9': Subscript out of range

For the code to work as written/amended, every value in 'Cost Center Summary'!A206:A340 must be a valid worksheet name. This also means no blanks.

If you're only listing worksheets from A206:A300, say, then you'll need to start looping backwards from row 300, rather than row 340.

Perhaps easiest if you check the size of the range dynamically using VBA. Let me know if you need help with this?