CODEWITHSUNDEEP
excelvba
suki
suki

Reputation: 63

Sorting worksheets in a workbook user defined

i have a question and i am new to VB. Please help me!

I have a workbook with different worksheets and i want to sort them according to the range of order that i give in "Csheet". This is the code below and it works fine. But i wanted to implement this on a different workbook. And it does not work. Can you help me with this please!!!!

Dim SortOrder As Variant
Dim Ndx As Long
With Worksheets("CSheet").Range("A1:A3")
    For Ndx = .Cells.Count To 1 Step -1
        Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
    Next Ndx
End With
End Sub

Upvotes: 0

Views: 123

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

Based on my comments, here is a more stuctured way to do it. I just quickly typed it and hence it is not tested. Let me know if you face any error.

The reason why your code will not work for the other workbook is because you are not specifying in which workbook you want the sheets to sort. You need to fully qualify your objects.

Sub Sample()
    Dim thisWb As Workbook, thatWb As Workbook
    Dim ws As Worksheet
    Dim i  As Long

    Set thisWb = ThisWorkbook
    Set ws = thisWb.Sheets("CSheet")

    Set thatWb = Workbooks("BlahBlah")

    For i = 3 To 1 Step -1
        With ws
            thatWb.Worksheets(.Cells(i, 1).Value).Move _
            before:=thatWb.Worksheets(1)
        End With
    Next i
End Sub

Upvotes: 1

Related Questions