Rearrange existing sheet order in Excel file

Help Studio
, , , , , , , , , , , , ,
1

Hi,
I’m trying to arrange excel sheets in customised order like (Query 1, Query 1 Result, Query 2, Query 3, Query 3 Result, Query 4, etc.,).
Query result sheet sometime not exists. If exists Query 1 after Query 1 Result, … and I have upto 46 sheets.
I’m not getting in same order when i ran process each time.

Please help me on this!
image

Thanks,

2

@koneruupendra111

You can use a macro for it…A little chatgpt would have given you the solution


Sub OrderSheets(sheetOrder As String)

    Dim sheetNames() As String
    Dim i As Integer
    
    ' Split the input string into an array of sheet names
    sheetNames = Split(sheetOrder, ",")
    
    ' Loop through the array of sheet names
    For i = LBound(sheetNames) To UBound(sheetNames)
        ' Move the sheet to the specified position
        Sheets(sheetNames(i)).Move Before:=Sheets(1)
    Next i

End Sub

Cheers

3

Hi @Anil_G ,
Thanks for response

I have multiple sheets like Query 1, Query 1 Result, Query 2, Query 3, Query 3 Result, Query 4, etc.,).
Not a single sheet name for Ex Query 1, Query 1 Result, etc.,

image

Thanks,

4

@koneruupendra111

Then input you need to give is a string with all the sheets names that you want to order in the same order you need separated by comma

Eg: sheet1,sheet3,sheet4

Cheers

5

Hi @Anil_G ,

Hi

This is not working in my case.

Actually, what I mean is I have some queries; for example whenever I run a query, the details of that query is stored in ‘query 1’ sheet and if this ‘query 1’ has data then need to generate subsequent sheet named with ‘query 1 Result’ if there’s no data then that query shouldn’t hold any resultant sheet which might be changed dynamically here.

As mentioned before Excel stores the sheets but shuffles after bot processing it I’m looking how this needs to be rearranged in an order as below

image

Thanks,

6

Hi @koneruupendra111 ,.

Have a look at this thread

Hope it helps.

Thanks
Gautham.

7

@koneruupendra111

Did you happen to use the above one?

Did you get any error?

If so do let us know we can solve…I believe you need only arranging of sheets

Cheers

8

You can also try 2 solutions

  1. Once your workbook is ready, read all sheets and based on name copy them to another workbook as in the order you want
  2. You can create a macro that moves sheets to respective given index and then move the sheets accordingly.