Macro Copy Help

[catherine9910]

Hey Everyone,

I need your help again. I have Sales data that I need to be copied and pasted into a Hot Spot workbook (both are excel) via Macro. The only problem is the name of the Sales data workbook changes each day. However, it is in the same file location/folder.

7-23-2021.csv

7-22-2021.csv 

7-21-2021.csv  and so on.

How do I write my VBA code to open a set folder then let me open a different file each time to copy and paste into my Hot Spot workbook?

[#2 · Accepted solution]

@catherine9910 

I hope you can use the code below as starting point.

Sub ProcessCSV()
    ' Change path as needed but keep the \ at the end
    Const strPath = "C:\MyFolder\"
    Dim strFile As String
    Dim wbkSales As Workbook
    Dim wbkCSV As Workbook
    With Application.FileDialog(1) ' msoFileDialogOpen
        .Filters.Clear
        .Filters.Add "CSV Files (*.csv)", "*.csv"
        .InitialFileName = strPath & "*.csv"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    Set wbkSales = ActiveWorkbook
    Set wbkCSV = Workbooks.Open(Filename:=strFile)
    ' Code to copy goes below
    ' ...
    wbkCSV.Close SaveChanges:=False
End Sub

[#3]

Thank you so much Hans that worked perfectly. You're the man!