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:
Please, HELP!
