none
Worksheet Order RRS feed

  • Question

  • I have an extremely large workbook, with approx. 100 worksheets

    At the front we have a register that is hyperlinked to each sheet for easy manipulation.

    Is there a way that I can "order" the worksheets based on how they appear on the register?

    Thanks

    Friday, August 26, 2016 1:32 PM

Answers

  • Hi lnzieBear,

    >> Is there a way that I can "order" the worksheets based on how they appear on the register?

    You could try Worksheet.Move to move worksheets. You could loop through the last one to first one, and then move the sheet after “Entity Register” one by one.

    Here is a simple code:

    Sub OrderSheetsRe()
    
        Dim sRegister As Worksheet
    
        Dim i As Long
    
        'loop A3 to A1 to get sheet entities
    
        Set sRegister = ActiveWorkbook.Worksheets("Register")
    
        'loop through A column from last row to first row
    
        For i = sRegister.UsedRange.Rows.Count To 1 Step -1
    
            'move worksheet by Worksheet.Move
    
            ActiveWorkbook.Worksheets(sRegister.Cells(i, 1).Text).Move after:=sRegister
    
        Next i
    
    End Sub
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 29, 2016 2:37 AM

All replies

  • You need to clarify some ... the worksheets reside on disk, why/how would you like to "reorder" them?

    Since the register contains links, you can reorder the links.


    Best regards, George

    Friday, August 26, 2016 2:11 PM
  • Hello,

    To clarify. I have an "entity" list with hyperlinks to each relevant worksheet.

    However, I would like to know if its possible that the order of the worksheets follows that of the entity list. Ie The workbook would look like

    WS 1 : Entity Register

    WS2  : First Entity

    WS3 : Second Entity

    WS4: Third Entity. and so on.

    Also, to clarify the entities are not in Alphabetically or numerical order.

    Thanks

    Friday, August 26, 2016 2:38 PM
  • I do not understand your answer. Maybe someone else can help you.

    Best regards, George

    Friday, August 26, 2016 2:41 PM
  • I guess it is possible. But pls put a bare sample of the file in one drive or dropbox.

    We can loop the 1st sheet and check the hyperlinks SubAddress property. Get the sheet name and then reposition them one by one.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, August 27, 2016 6:58 PM
    Answerer
  • Hi lnzieBear,

    >> Is there a way that I can "order" the worksheets based on how they appear on the register?

    You could try Worksheet.Move to move worksheets. You could loop through the last one to first one, and then move the sheet after “Entity Register” one by one.

    Here is a simple code:

    Sub OrderSheetsRe()
    
        Dim sRegister As Worksheet
    
        Dim i As Long
    
        'loop A3 to A1 to get sheet entities
    
        Set sRegister = ActiveWorkbook.Worksheets("Register")
    
        'loop through A column from last row to first row
    
        For i = sRegister.UsedRange.Rows.Count To 1 Step -1
    
            'move worksheet by Worksheet.Move
    
            ActiveWorkbook.Worksheets(sRegister.Cells(i, 1).Text).Move after:=sRegister
    
        Next i
    
    End Sub
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 29, 2016 2:37 AM