[RESOLVED]These WorkSheets look OK but are They?

[maurice_whittaker]

These Worksheets look OK but are they?

In my workbook, I have 8 worksheets which are visible in their “correct” order:

Income, Expenditure, Transfers, Standing Orders, Consolidation, Schedule B, Budget, and lastly,
Help Sheet

Viewed in Project Explorer in VB (Version 6.5), however, they appear in this order:

Sheet10 (Schedule B), sheet11 (Budget), sheet4 (Income), sheet5 (Expenditure), sheet6 (Transfers),
sheet7 (Standing Orders), sheet8 (Help Sheet) and lastly sheet9 (Consolidation)

I am puzzled as to why the worksheets (in VB) have got re-arranged in their order although I can sort of understand why the “VB” sheet numbers may change. How can I ensure that it doesn’t happen again, once I have sorted it? Also, what is the best way to sort it? I feel, in my bones, that the error arises because the Help Sheet has broken the continuity of the range as it is not supposed to appear in the middle of the list (and not supposed to be moved either).

I am using the following code to Move the sheets (Income through to Budget) from my main workbook into a “Target” workbook which produces a “Subscript out of Range” error.
Thanks in anticipation.

Sub MoveSheets()
' Recorded Macro

    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Maurice\My Documents\My Spreadsheets\Dawn\Church Accounts 10-11.xlsx"
'    ActiveWindow.WindowState = xlMinimized
'    ActiveWindow.WindowState = xlMaximized
    Sheets(Array("Income", "Expenditure", "Transfers", "Standing orders", _
        "Consolidation", "Schedule B", "Budget")).Select
    Sheets("Income").Activate
    Sheets(Array("Income", "Expenditure", "Transfers", "Standing orders", _
        "Consolidation", "Schedule B", "Budget")).Move Before:=Workbooks( _
        "Church Accounts 10-11.xlsx").Sheets(1)
 ' Disable the warning about Sheets may contain data
    Application.DisplayAlerts = True
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete                ' Delete the Temporary sheets from the Target WorkBook.
    Application.DisplayAlerts = True
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

[westconn1]

in the vbproject they are ordered by codename, sorted as strings, so 10, 11 or 101 come before 2
codename of sheets never change and are applied automatically when any new sheet is added, codenames of deleted sheets are not reused within a workbook

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete

i am purely guessing this is the line that causes your error, as you have not specified which line the error occurs, but it would appear that all your previous code refers to source workbook not the target

you should avoid using select, activate, selection or active anything, instead refer to fully qualified ranges or objects, that is, specify which workbook contains the sheets in all cases, to avoid confusion and errors

posts about excel vba should be in office development

[maurice_whittaker]

Thanks again Pete, I removed the "select" and used ThisWorkBook and it works - (hopefully for good).
Keep up the good work!
Maurice