VBA to open 2 workbooks(name changes on 1), copy and paste data and drag down existing formula

[cdalgorta]

Hi,
Thank you in advance. This one is a bit long. Sorry!

So I download a file daily and I use that to replace the data on another file which is 1 of my database excels for my main template.
The VBA that I'd need would be the following:
* Open both Excels(I'm guessing they have to be opened for this to work).
The daily new data always goes to my "Downloads" file, the name always starts with "Additional info looker Standard Unlimited " followed by the date and then random characters(I delete the one from the previous day, so there will always only be 1 file in my downloads that starts with "Additional info looker Standard Unlimited ".
There is only 1 sheet on the workbook and the name is always "Additional info looker Standard".

I need to copy all columns from A to S. The number of rows always changes(could be more, could be less), but it's between 100k to 200k(if that matters). Also, not sure if this matters as well, but A1 is empty as you can see in the image.

*By the way, this daily download file is a .csv file.*

Now, the file I need to paste the data in, is here:
C:\Users\cristian\Desktop\Standard Aging Local
Name: Additional Info Lookup Data
Sheet name: Additional Info Lookup

There are formulas on columns T and U, so I was thinking that the best way to do it, would be with a VBA that deletes everything below Row 3, paste the data from the previous file into this one from A2 to S2 all the way down, and then drag the formulas on T2 and U2 all the way down until the last row of the new data. And that would finish what I need.

P.S. If possible, I think a VBA that turns "Calculation Options" to "Manual" before opening the 2nd file with the formulas, and then another one that triggers "Calculate Now" after dragging down the formulas on T2 and U2, would make this better. For some reason, "sometimes" when I open the workbook with the formulas, it starts recalculating the formulas even though I just opened it and nothing has changed. I have to remind myself to click "manual" before I open any excel with formulas or I have to wait for the thing to finish calculating...

P.P.S If possible as well, a VBA to close the 1st file at the very end would be great. I know the VBA to close a file with a fixed name, but not when the file name changes and only part of the name is fixed, "Additional info looker Standard Unlimited ".

Thank you so much in advance! I'm thinking of paying for a Coursera VBA course so that I can start helping others here as well instead of just asking for help. Hahaha. There are quiet a few, so any recommendations from the experts would be greatly appreciate it!

Cristian

 

[cdalgorta]

Your past couple of posts are not making sense.

Post #17 shows a display of the destination sheet. Post #19 shows that you are getting error when creating the button to click on.

Please follow the steps exactly as described in post #16 and then report back the result.

I've tried a few times to redo the whole thing. The steps are very simple to follow, so I don't think I'm making any mistakes with the steps.

As per the different errors:

When I click on the button, I get the following after workbook "Additional Info Lookup Data" opens:

The same happens when I click play on the "Sheet1 (Code)". And it doesn't say debug(only ok and help), so I don't know how to check which line is giving Subscript out of range error.

When I click "play" on the VBA Module though, I get the following:

 

[johnnyL]

Ok. Let's try this, a file already set up for you.

Link to file

Click the button in that file.

 

[cdalgorta]

Same thing when clicking the button.

Ok. Let's try this, a file already set up for you.

Link to file

Click the button in that file.

 

[cdalgorta]

Ok. Let's try this, a file already set up for you.

Link to file

Click the button in that file.

Just in case this may be it.

DestinationPath = Environ("USERPROFILE") & "\Desktop\" & "Standard Aging Local\"

This is my work computer, so I'm not the admin. There are 4 folders on my Users folder and the one I use is the one with my name. Could that be it?

 

[johnnyL]

It appears that the CSV file and the Destination file is being loaded, according to your pics.

Do you get the same error with the demo csv (10 lines) that you uploaded earlier?

 

[cdalgorta]

It appears that the CSV file and the Destination file is being loaded, according to your pics.

Do you get the same error with the demo csv (10 lines) that you uploaded earlier?

The CSV does not open. Only the Destination file opens.
Also, just tried it with the test file in the downloads folder and the same thing happens.

 

[johnnyL]

You will not 'see' the csv file open or close. Your clue that it opened is that you are getting data.

Do you know how to set a 'breakpoint in the VBE window?

 

[cdalgorta]

You will not 'see' the csv file open or close. Your clue that it opened is that you are getting data.

Do you know how to set a 'breakpoint in the VBE window?

Hahah. Good to know. But then it's not loading either. There are only 10 columns(test file) of data in the downloads folder and the destination file has more than 10 rows of data(the same from the previous time).

Is the breakpoint when you click F9 and it turns red?

 

[johnnyL]

You should never hit that section of code if you are clicking on the button that was created on Sheet1.

 

[johnnyL]

Can you post a pic of the button you are clicking on?