Excel VBA Copy Data from Workbook

[singapore99]

Hey All, I am trying to copy data from one workbook into another. The data that is being copied is in an excel file that exported from a website that has a changing file name (something that I do not have control over). The front half of the file name remains the same, but it is the back half, where it has the date is located, changes the date to whenever I export the file. I have the code where I can copy the data from the one workbook to the other with no problem. However, my issue is that for every day I export the data, I would need to update the code with the new file name. Any thoughts on how I can create a code that would allow for a fluctuation in the file name. I have pasted my basic code that I am using and underlined the issue that I am having. Any advice or help would be greatly appreciated.

Sub Workbook_Copy_All()
'
' Workbook_Copy_All Macro
'
'
Windows("Suicide Prevention Users- 10-7-2014 (1).xls"). _
Activate
Range("A1:K100").Select
Range("G1").Activate
Cells.Select
Range("G1").Activate
Selection.Copy
Windows("Tracker Test Coding.xlsm").Activate
ActiveSheet.Paste
Range("A1:A3").Select
End Sub

 

[StephenCrump]

Welcome to MrExcel!

Perhaps something like this:

Sub Workbook_Copy_All()

    Dim wkbFrom As Workbook, wkbTo As Workbook
    
    Set wkbFrom = Workbooks("Suicide Prevention Users- " & Format(Now, "mm-d-yyyy") & "(1).xls")
    Set wkbTo = Workbooks("Tracker Test Coding.xlsm")
    
    wkbFrom.Sheets("Sheet1").Range("A1:K100").Copy Destination:=wkbTo.Sheets("Sheet1").Range("A1")
    
End Sub

 

[singapore99]

@StephenCrump: I tried the code that you provided but unfortunately it didn't work. I am getting a "Run-time error '9': Subscript out of range". When I click the debug button, it highlights the first Set wkbFrom = line. Do you have any suggestions to fix this? I have tried the code with today's info, recording me selecting the workbook and then copying exactly how the workbook appears and then making the adjustments from what you suggested. I am still new to VBA coding and don't know exactly what the error means. Usually, if I get an error like this, I re-evaluate my process and change the route I was planning on using. However, this current course of actions is not something I really can change. In the past, what I have been doing is just copying the data and pasting it. However, with what I plan on using this for, if I can take out extra steps (such as copying and pasting from two different workbooks and then running the module), the more streamline this would be, the better it would help my company. Unfortunately, nobody is verse in VBA coding, so my resources are very limited. What goal for this Excel program to do is that all I would have to do is select the course, and it would do everything I would need it to do (copy and paste with the two workbooks, sort and then move the data). Any additional advice or possible coding would be appreciated.

 

[StephenCrump]

Based on your original code, my code expects to find a workbook already open called: Suicide Prevention Users- 10-9-2014(1).xls (i.e. assuming Now is 9 Oct 2014).

"Run-time error '9': Subscript out of range" means that this workbook is not open. This means either:

1) That you haven't opened the workbook, in which case, do you want VBA to do this for you? If so, VBA will need to know the file path to locate the workbook.

OR

2) That the workbook is open, but the name doesn't match exactly, e.g. including the placement of all space and hyphen characters.

I wasn't sure from your code whether Tracker Test Coding.xlsm was ThisWorkbook, i.e. the workbook running the VBA code, or another workbook?

The copy/paste is also illustrative only, as I wasn't sure what you were copying from, and what you were pasting to?

See if you can debug the error, and then perhaps describe in more detail what you're trying to do (copy, paste, sort, move?) as there may be smarter ways to achieve this.

 

[singapore99]

@StephenCrump: Thank you for your help. Between your coding and some deeper research, I was able to find a resolution that suffices what I need the code to do and some additional streamlining that helps me out in the long run. Additionally, I was able to provide a backup of the online information to a folder which would allow me to make printouts if requested.

 

[StephenCrump]

Thanks for posting back.

And well done for working it out yourself!