Hello everyone,
I'm pretty new to Excel so I've been following code from someone else online and plan to tailor it to my needs. I'm currently having this error.
The error is: "Run-time error '1004': '111_02_03_2019.csv' could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open a file from your list of most recently used files, make sure that the file has not been renamed, moved or deleted."
Any ideas on what's wrong? I haven't been able to find anything else on it online.
Option Explicit
Dim wsMerge As Worksheet
Dim RowInsert As Long
' which row can I insert my dataset?
Sub Merge_PolarFiles()
Const FolderPath As String = "C:\Users\anip\Desktop\File Retrieve Polar 007 - Copy\"
' need to choose location
Dim Files As String
Dim wbTemp As Workbook
Dim LastRow As Long
Set wsMerge = ThisWorkbook.Worksheets("Merge")
Call ClearMergeWorksheet
RowInsert = 2
Files = Dir(FolderPath + "*.csv")
Do Until Files = ""
Set wbTemp = Workbooks.Open(Files)
With wbTemp.Worksheets(1)
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("M2:LastRow,AU2:LastRow").Copy
wsMerge.Range("A" & RowInsert).PasteSpecial xlPasteValues
Application.DisplayAlerts = False
wbTemp.Close False
Application.DisplayAlerts = True
RowInsert = RowInsert + LastRow - 1
End With
Files = Dir()
Loop
MsgBox "File Merge Complete", vbInformation
End Sub
Private Sub ClearMergeWorksheet()
Dim LastRow As Long
With wsMerge
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If 2 > LastRow Then Exit Sub
'.Range("A2:N" & LastRow).ClearContents
.Range("A2:CE" & LastRow).ClearContents
End With
End Sub
Bookmarks