Runtime Error 1004: Method 'Range' of object '_Global' failed in excel vba

821 Views Asked by At

I am trying to export macro recorded dataset into a separate csv file. For that, I have the following vba code, within a workbook, on "BeforeClose" event:

       '
       ' Exporting Data:
       '

        Dim wbkExport As Workbook
        Dim shtToExport As Worksheet
        Dim Path1 As String

      (4) Path1 = Range("Config!Path")

       Set shtToExport = ThisWorkbook.Worksheets("Sheet1")     'Sheet to export as CSV
       Set wbkExport = Application.Workbooks.Add
       shtToExport.Copy before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
       Application.DisplayAlerts = False                       'Possibly overwrite without asking
      'FileFormat:=xlOpenXMLWorkbook

        wbkExport.SaveAs Filename:=Path1, FileFormat:=xlCSV

        Application.DisplayAlerts = True
        wbkExport.Close savechanges:=False
        Application.DisplayAlerts = False
        Application.Quit

But I am getting "Runtime Error 1004: Method 'Range' of object '_Global' failed in excel vba" error

When I tried to debug, it highlights - Path1 = Range("Config!Path") - string in my code. (I referenced as (4) in the code).

In my Macro Enabled worksheet I have "Config" sheet, with the following content:

enter image description here

Please, HELP!

0

There are 0 best solutions below