Code works on other workbook but not this workbook

Question

Hi i have this code that unmerges the cells, it runs when it is in another workbook but have an error when i run it in its orignal workbook bdncasemacro.xlsm, it gives the error code 9. is there anything i can do to resolve this as i cant figure out the error as it runs in the other workbook .

Dim wscopy As Worksheet
Dim wspaste As Worksheet
Workbooks.Open "C:\Users\attkeej1\Desktop\testing\jobsummarydownloadmacro.xls"
Workbooks.Open "C:\Users\attkeej1\Desktop\testing\bdncasemacro.xlsm"
    Set wscopy = Workbooks("jobsummarydownloadmacro.xls").Worksheets("Report")
    Set wspaste = Workbooks("bdncasemacro.xlsm").Worksheets("Raw")

 wscopy.Range("A1:AZ1000").Copy _
    wspaste.Range("A1")

    wspaste.Cells.Select
        With Selection
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
            End With
    wspaste.Rows("1:5").Delete Shift:=xlShiftUp
    wspaste.Cells.Select
    Selection.ColumnWidth = 8.29
    wspaste.Range("C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA").Select
    wspaste.Range("Y1").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    wspaste.Range( _
        "C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA,AC:AD,AF:AH,AJ:AJ,AL:AM,AO:AO,AQ:AR,AT:AU"). _
        Select
    wspaste.Range("AT1").Activate
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    wspaste.Range( _
        "C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA,AC:AD,AF:AH,AJ:AJ,AL:AM,AO:AO,AQ:AR,AT:AU,AW:AY" _
        ).Select
   wspaste.Range("AW1").Activate
    Selection.Delete Shift:=xlToLeft
    wspaste.Range("AG23").Select
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    wspaste.Cells.Select
    Selection.ColumnWidth = 9.71
    Selection.ColumnWidth = 13.71
    wspaste.Range("D14").Select

End Sub

however it doesnt work when i try to run it in the original workbook giving the error code 9

Workbooks.Open "C:\Users\attkeej1\Desktop\testing\jobsummarydownloadmacro.xls"
    Set wscopy = Workbooks("jobsummarydownloadmacro.xls").Worksheets("Report")
    Set wspaste = ThisWorkbook.Worksheets("Raw")
 wscopy.Range("A1:AZ1000").Copy _
    wspaste.Range("A1")

    wspaste.Cells.Select
        With Selection
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
            End With
    wspaste.Rows("1:5").Delete Shift:=xlShiftUp
    wspaste.Cells.Select
    Selection.ColumnWidth = 8.29
    wspaste.Range("C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA").Select
    wspaste.Range("Y1").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    wspaste.Range( _
        "C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA,AC:AD,AF:AH,AJ:AJ,AL:AM,AO:AO,AQ:AR,AT:AU"). _
        Select
    wspaste.Range("AT1").Activate
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    wspaste.Range( _
        "C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA,AC:AD,AF:AH,AJ:AJ,AL:AM,AO:AO,AQ:AR,AT:AU,AW:AY" _
        ).Select
   wspaste.Range("AW1").Activate
    Selection.Delete Shift:=xlToLeft
    wspaste.Range("AG23").Select
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    wspaste.Cells.Select
    Selection.ColumnWidth = 9.71
    Selection.ColumnWidth = 13.71
    wspaste.Range("D14").Select

End Sub

is there advice, thanks in advance

Which one? It would help to remove the unneeded parts of your code (eg all of the ScrollColumn lines) — Tim Williams, Dec 18 '19 at 01:37
the second code dont work, it doesnt work when i move the code to bdncasemacro.xlsm, the unmerging part is from macro recording. — programmer, Dec 18 '19 at 01:39
Are you sure you are getting the error on `wspaste.Cells.Select` and not on `Set wspaste = ThisWorkbook.Worksheets("Raw")`? Few tips. **1.** Avoid using `wspaste.Cells`. Identify your actual range and work with it. **2.** Get rid of all `ActiveWindow.ScrollColumn` code lines **3.** Avoid the use of `.Select/.Activate` You may want to see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — Siddharth Rout, Dec 18 '19 at 04:57

Noah Bridge · Answer 1 · 2019-12-18T15:35:57.380

Short-term solution

I am not sure why you would be getting error 9, which is the "Subscript out of range" message in a call to Select. Therefore, I will assume for now that you are getting a different error.

If so, it seems to be because a Select on a range fails if the workbook containing the range is not the active workbook. When you run the code from a different workbook, you are opening "bdncasemacro.xlsm" second, meaning that that becomes the active workbook (the workbook opened last remains active). Therefore, there is no problem selecting cells on it, since wspaste is in bdncasemacro.xlsm. However, when you execute the code from within bdncasemacro.xlsm, you only open the other workbook, which makes it the active workbook, and then you create wspaste using ThisWorkbook. Therefore, wspaste is a worksheet on the workbook that is not active. This will definitely cause wspaste.Cells.Select to fail.

A quick solution would be to force bdncasemacro.xlsm to become the active workbook by inserting this statement after opening the first workbook is opened:

ThisWorkbook.Activate

This line would, in essence, be the replacement for the second Workbooks.Open when the code is run from another workbook. You must also make sure that the worksheet containing the cells is the active one; otherwise, the Select can fail even if the workbook is active. To ensure this, you must add the following line before the first wspaste.Cells.Select:

wspaste.Activate

Long-term solution

Having said this, I believe that a more permanent solution is to avoid .Select and .Activate altogether. The macro recorder adds that code because it is mechanically recording every action you take, but, in the code, you generally do not need to select cells in order to perform actions on them, as is described in the following link, under the section "Avoid Select and Activate Whenever Possible" (you will have to scroll down to the middle of the long page to get to the section) ...

http://www.excelcampus.com/vba/select-vs-activate-method/

The code also has several Select and Activate statements that "overwrite" each other, so to speak, meaning that you can delete the intermediate Select and Activate statements and only leave the last Select and Activate statements before actual work is done on the worksheet.

It also seems as if the ScrollWindow calls are not necessary to perform the required actions. Those calls must have been added by the macro recorder as you were scrolling along the worksheets to perform the actions.

Also, it seems as if the procedure is not closing the workbooks after opening them. That could be playing a role in the error 9 that you may be seeing at some point.

Taking all of this into account, the code could be reduced to this:

    Dim wbDownload As Workbook
    Set wbDownload = Workbooks.Open("C:\Users\attkeej1\Desktop\testing\jobsummarydownloadmacro.xls")

    Set wscopy = wbDownload.Worksheets("Report")
    Set wspaste = ThisWorkbook.Worksheets("Raw")

    wscopy.Range("A1:AZ1000").Copy wspaste.Range("A1")
    With wspaste.Cells
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
    End With
    wspaste.Rows("1:5").Delete Shift:=xlShiftUp

    wspaste.Cells.ColumnWidth = 8.29

    With wspaste.Range("C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA,AC:AD,AF:AH,AJ:AJ,AL:AM,AO:AO,AQ:AR,AT:AU,AW:AY")
        .Delete Shift:=xlToLeft
    End With

    With wspaste.Cells
        .ColumnWidth = 9.71
        .ColumnWidth = 13.71
    End With

    wbDownload.Close

I hope I did not overdelete from your original code. If so, I apologise, but I hope the above is of help in shortening the code.

Code works on other workbook but not this workbook

1 Answers1