none
Order of sheets in array copy? RRS feed

  • Question

  • Hi All,

    I have the following command which creates a workbook with all the sheet names in the array below.

    Worksheets(Array(gcstrVersionControl, gcstrInstructions, gcstrAcctMulti, _
                      gcstrProject, gcstrBusJust, gcstrPositions, gcstrOutputSummary )).Copy

    subsequently I save as a file name and then copy data from other arrays e.g. gvarProjects and gvarPostions in the example below.

    ActiveWorkbook.SaveAs Filename:=strWorkbookName, _
                          FileFormat:=xlOpenXMLWorkbook

    ' copy Project Data
    ActiveWorkbook.Sheets(gcstrProject).Select
    Range("B13").Resize(gcintProjRows, gcintPosCols).Value = gvarProjects


    ' Copy Positions data
    ActiveWorkbook.Sheets(gcstrPositions).Select
    Range("B11").Resize(gcintPosRows, gcintPosCols).Value = gvarPositions

    My question is: how to I control the order of the sheets in the new workbook; I thought it would depend on the order I had in the array command above - but it does not seem to.

    I hope this makes sense - can anyone help please?

    Peter

    Tuesday, October 18, 2016 2:13 PM

Answers

  • The copied sheet will be in the order they were in the source workbook. For example, if you have (from left to right) Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5 the line

    Worksheets(Array("Sheet3", "Sheet1", "Sheet4")).Copy

    will result in a new workbook with (from left to right) Sheet1, Sheet3 and Sheet4, because that is the order they were in.

    One workaround is to copy only the first sheet (gcstrVersionControl) initially, then copy the next one (gcstrInstructions) after it, etc.

    Another workaround is to keep the code that you have, then move the sheets into the desired order.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Marked as answer by py1 Tuesday, October 18, 2016 3:29 PM
    • Edited by Hans Vogelaar MVPMVP Tuesday, October 18, 2016 4:24 PM
    Tuesday, October 18, 2016 3:23 PM

All replies

  • The copied sheet will be in the order they were in the source workbook. For example, if you have (from left to right) Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5 the line

    Worksheets(Array("Sheet3", "Sheet1", "Sheet4")).Copy

    will result in a new workbook with (from left to right) Sheet1, Sheet3 and Sheet4, because that is the order they were in.

    One workaround is to copy only the first sheet (gcstrVersionControl) initially, then copy the next one (gcstrInstructions) after it, etc.

    Another workaround is to keep the code that you have, then move the sheets into the desired order.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Marked as answer by py1 Tuesday, October 18, 2016 3:29 PM
    • Edited by Hans Vogelaar MVPMVP Tuesday, October 18, 2016 4:24 PM
    Tuesday, October 18, 2016 3:23 PM
  • Hi Hans

    Interesting - thanks. 

    I had actually amended my code to move the sheets in the order I wanted as per your second workaround.

    thanks anyway!

    kind regards,

    Peter

    Tuesday, October 18, 2016 3:29 PM