I have a macro to open up multiple csv files and to copy the data
However, when Running the macro, I get a run time error "Object variable or with block variable not set"
Kindly test and amend my code
Code:
Sub Open_MultipleFiles()
Clear_Data
Dim fDialog As Object, varFile As Variant
Dim nb As Workbook, tw As Workbook, ts As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.CutCopyMode = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set fDialog = Application.FileDialog(3)
ChDir "C:\extract"
With fDialog
.Filters.Clear
.Filters.Add "Excel files", "*.csv*"
.Show
For Each varFile In .SelectedItems
Set nb = Workbooks.Open(Filename:=varFile, local:=True)
nb.Sheets(1).Range("A1:AZ500").Copy Destination:=ThisWorkbook.Sheets("Imported Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
nb.Close False
Next
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.CutCopyMode = True
End With
With Sheets("Imported Data")
.Range("a1").EntireRow.Delete
End With
End Sub
Sub Clear_Data()
Dim LR As Long
With Sheets("Imported Data")
LR = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("A1:AZ" & LR).ClearContents
End With
End Sub
However, when Running the macro, I get a run time error "Object variable or with block variable not set"
Kindly test and amend my code