Copy/Paste with Variable Workbook Name

[dwool40]

I am trying to run a simple macro that will copy from a downloaded workbook with a variable name to a workbook with a permanent name.

Sub CopyUA()
Workbooks("UnitAvailabilityDetails11_08_2021.xlsx").Worksheets("Report1").Range("A10:R300").Copy
Sheets("Unit Availability").Range("A8").PasteSpecial Paste:=xlPasteValues
End Sub

The UnitAvailabilityDetails11_08_2021.xlsx changes daily with the date. I'm sure this is a simple solution so thanks in advance!

 

[Joe4]

One way:

Sub CopyUA()
    Dim wbName As String
    wbName = "UnitAvailabilityDetails" & Format(Date, "mm_dd_yyyy") & ".xlsx"
    Workbooks(wbName).Worksheets("Report1").Range("A10:R300").Copy
    Sheets("Unit Availability").Range("A8").PasteSpecial Paste:=xlPasteValues
End Sub

 

[dwool40]

One way:

Sub CopyUA()
    Dim wbName As String
    wbName = "UnitAvailabilityDetails" & Format(Date, "mm_dd_yyyy") & ".xlsx"
    Workbooks(wbName).Worksheets("Report1").Range("A10:R300").Copy
    Sheets("Unit Availability").Range("A8").PasteSpecial Paste:=xlPasteValues
End
[/QUOTE]

Seems so simple. THANK YOU!

Another question maybe for another thread. Is it possible to do a wildcard for a variable workbook name? Same copy/paste but for a different workbook. This filename adds a number if there is an existing file when downloaded.

DataGridExport.xlsx can have a (1), (2), etc. at the send of the file name.

 

[Joe4]

Another question maybe for another thread. Is it possible to do a wildcard for a variable workbook name? Same copy/paste but for a different workbook. This filename adds a number if there is an existing file when downloaded.

DataGridExport.xlsx can have a (1), (2), etc. at the send of the file name.

It might possibly have to be a new question, but the questions I have for you is:
- How is this file being opened in the first place (you code is running like this file is already opened)?
- How many other Excel files are open at the same time?

The reason why I ask is because depending on the answers to those questions, we maybe able to dynamically "capture" the file name as it is being opened, or by process of elimination (if it is the only other open Excel file at the time).

 

[dwool40]

It might possibly have to be a new question, but the questions I have for you is:
- How is this file being opened in the first place (you code is running like this file is already opened)?
- How many other Excel files are open at the same time?

The reason why I ask is because depending on the answers to those questions, we maybe able to dynamically "capture" the file name as it is being opened, or by process of elimination (if it is the only other open Excel file at the time).

There are 2 workbooks downloaded every morning. Currently, my team is using copy/paste to import the information. However, half of my team of 90 is computer challenged. I actually had to teach someone to right-click. Now they can simply click a button to import each downloaded workbook into the master but, with the (#) being added they need to empty the downloads folder before they can run the new reports.

The process is:
Empty download folder
Open master workbook
Download and open both reports
Click buttons to import (copy/paste) new info
Save master and close the 2 downloaded reports

I hope that makes sense. If it is too complicated to account for the (#) variable it's not that big of a deal. I was looking for a way for them to not have to empty the downloads folder every day.

 

[Joe4]

Empty download folder
Open master workbook
Download and open both reports
Click buttons to import (copy/paste) new info
Save master and close the 2 downloaded reports

It seems to me like you should/could be able to automate ALL of these steps in VBA code, rather than doing these steps manually and separately.