VBA give error on excel 2013

[aliaslamy2k]

Dear Experts,

I have below code which works perfectly fine in excel 2019 but it gives me an error when I run the same in excel 2013 on another laptop.
Error is "Error Occured while exporting. Try again". Please help if the code needs to be modified.



Sub Export_to_excel_ActualVSBudget()
On Error GoTo err



Dim xlApp As New Excel.Application
Dim xlWB As New Workbook

Set xlWB = xlApp.Workbooks.Add
'xlWB.Add
xlApp.Visible = False


ThisWorkbook.Activate
Range("A9").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

xlApp.Visible = True
xlWB.Activate
xlWB.Sheets("Sheet1").Select
xlWB.Sheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues
xlWB.Sheets("Sheet1").Cells.Select
xlWB.Sheets("Sheet1").Cells.EntireColumn.AutoFit
xlWB.Sheets("Sheet1").Range("A2").Select
Exit Sub
err:
MsgBox ("Error Occured while exporting. Try again")

End Sub

 

[dmt32]

Hi,
the error is a custom message not the actual error you are getting

untested but try this update to the code which if problem still persists should report the actual error.

Sub Export_to_excel_ActualVSBudget()
    Dim xlApp       As New Excel.Application
    Dim xlWB        As New Workbook

   
    On Error GoTo myerror
   
    Application.ScreenUpdating = False
    Set xlWB = xlApp.Workbooks.Add
   
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells(9, 1).Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 8, _
                            .Cells(9, .Columns.Count).End(xlToLeft).Column).Copy
    End With
   
    With xlWB.Sheets("Sheet1")
        .Range("B2").PasteSpecial Paste:=xlPasteValues
        .UsedRange.EntireColumn.AutoFit
    End With
   
myerror:
    With Application
        .CutCopyMode = False: .ScreenUpdating = True
    End With
    If err <> 0 Then MsgBox (Error(err)), 48, "Error"
End Sub

You will need to change the name of the sheet you are copying where shown in BOLD

Dave

 

[aliaslamy2k]

Hi Dave,

I am getting error when i run the macro "Subscript out of range"

 

[dmt32]

That indicates code cannot find the worksheet

change this line

With xlWB.Sheets("Sheet1")

to this

With xlWB.Sheets(1)

and did you change the name of the worksheet you are copying where shown in BOLD?

 

[aliaslamy2k]

Hi Dave,

I run the Macro, but nothing is happening, Below is the code i used.

Sub Export_to_excel_ActualVSBudget()
Dim xlApp As New Excel.Application
Dim xlWB As New Workbook


On Error GoTo myerror

Application.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Add

With ThisWorkbook.Worksheets("Agency")
.Cells(9, 1).Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 8, _
.Cells(9, .Columns.Count).End(xlToLeft).Column).Copy
End With

With xlWB.Sheets(1)
.Range("B2").PasteSpecial Paste:=xlPasteValues
.UsedRange.EntireColumn.AutoFit
End With

myerror:
With Application
.CutCopyMode = False: .ScreenUpdating = True
End With
If err <> 0 Then MsgBox (Error(err)), 48, "Error"
End Sub

 

[dmt32]

sorry, without thinking I deleted a line code

place line of code where shown in BOLD & see if resolves.

With xlWB.Sheets(1)
        .Range("B2").PasteSpecial Paste:=xlPasteValues
        .UsedRange.EntireColumn.AutoFit
    End With
    
    xlApp.Visible = True

Dave