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]

Once you hit that first yellow line in your last picture ... that is a loop to go through all of the rows of the csv file data.

At that point you can select a new break point of the red dot shown at the bottom of your last pic. Instead of continuing to hit the 'F8' button when you are in the code loop, you can tap the 'F5' button once and it should run all the way to where you set the new break point.

That's what I was doing, but like I said, seems like that one in red in the picture is the issue. Error triggers when I have that as the breakpoint.

 

[johnnyL]

It doesn’t say what the error is?

 

[cdalgorta]

It doesn’t say what the error is?

It doesn't give me the option to debug. That error message just pops up. Unless there is another way to see what the error is?

 

[johnnyL]

Without seeing the rest of your CSV data, it sounds like something in the CSV data is not being 'handled properly' & probably looking to add more columns than what are expected.

To see if this is the problem, try changing the following line, which is 1 line above the most recent code you pictured:

    ReDim Partitioned_CSV_FileArray(1 To UBound(All_CSV_RowsFromCSV_FileArray), 1 To 21)        ' Set rows/columns for Partitioned_CSV_FileArray

to:

    ReDim Partitioned_CSV_FileArray(1 To UBound(All_CSV_RowsFromCSV_FileArray), 1 To 30)        ' Set rows/columns for Partitioned_CSV_FileArray

If that allows the code to finish, then we will have to figure out what has not been handled properly in the CSV data. You will be able to spot the issue in the destination sheet by any lines of data that extend past the U column.

 

[cdalgorta]

Without seeing the rest of your CSV data, it sounds like something in the CSV data is not being 'handled properly' & probably looking to add more columns than what are expected.

To see if this is the problem, try changing the following line, which is 1 line above the most recent code you pictured:

    ReDim Partitioned_CSV_FileArray(1 To UBound(All_CSV_RowsFromCSV_FileArray), 1 To 21)        ' Set rows/columns for Partitioned_CSV_FileArray

to:

    ReDim Partitioned_CSV_FileArray(1 To UBound(All_CSV_RowsFromCSV_FileArray), 1 To 30)        ' Set rows/columns for Partitioned_CSV_FileArray

If that allows the code to finish, then we will have to figure out what has not been handled properly in the CSV data. You will be able to spot the issue in the destination sheet by any lines of data that extend past the U column.

Hi,
So it works up to here(image) with a breakpoint, but if I press F8 after this, it gives the same error.

 

[johnnyL]

Not sure what other assistance I can offer without having a csv file that produces the issues you are facing.

 

[cdalgorta]

Not sure what other assistance I can offer without having a csv file that produces the issues you are facing.

I believe I found the issue. Please try to use this sample and run your macro. It will run it, but look what happens with the values on the H column(PO #). There are some that are even longer on the PO # column.

Gofile - Cloud Storage Made Simple

Secure, fast and free cloud storage solution. Upload and share files instantly.

gofile.io

Edit:

P.S. In case this matters: Depending on the customer, the same could happen on columns F and G. Not as many as in column H, but some rows could have 2 or 3 references in the same cell separated by a comma, space or underscore. They also use colon and dashes, but not for different references, but the same reference could have colon or dashes as part of the name.

 

[johnnyL]

Got the same error that you have been reporting.
Now I need to track down what is causing it & how to handle it properly.

 

[cdalgorta]

Got the same error that you have been reporting.
Now I need to track down what is causing it & how to handle it properly.

Thank you so much and apologies for all the issues I'm giving you Johnny

 

[johnnyL]

No worries.