(Solved) – Code works on other workbook but not this workbook

  • by
(solved)-–-code-works-on-other-workbook-but-not-this-workbook

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:Usersattkeej1Desktoptestingjobsummarydownloadmacro.xls"
Workbooks.Open "C:Usersattkeej1Desktoptestingbdncasemacro.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:Usersattkeej1Desktoptestingjobsummarydownloadmacro.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

Leave a Reply

Your email address will not be published. Required fields are marked *