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

cdalgorta

Board Regular
Joined
Jun 5, 2022
Messages
87
Office Version
  1. 365
Platform
  1. Windows
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".
1658079371265.png


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.
1658076462105.png


*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
1658076750843.png

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.
1658077449548.png


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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Couple things:

1) we can see the formula in you post that is in T2, Can you show what the formula in U2 is?
2) Can you provide a link to a small sample of the .csv file so we can see its formatting?
 
Upvote 0
Couple things:

1) we can see the formula in you post that is in T2, Can you show what the formula in U2 is?
2) Can you provide a link to a small sample of the .csv file so we can see its formatting?
Hi Johnny,
=ROUND(O2,2)
1658081296260.png


Edit: My bad, I miss the "2)" point before replying. Give me a few minutes.
 
Upvote 0
Thank you for that info.

As for #2? A link to a small sample of the .csv file?
 
Upvote 0
Thank you for that info.

As for #2? A link to a small sample of the .csv
Sorry. I keep trying to download this XL2BB to show the data, but it does not download. Do you know how to fix this? It should not be protected view. It's a new file. Sorry. I don't see the upload attachment in here.
1658082111237.png
 
Upvote 0
You can't upload files to this site. You can upload the sample file to any of the numerous free sites that allow file uploads and then provide the link to the file. I normally use this site, but there are plenty other sites available.
 
Upvote 0
You can't upload files to this site. You can upload the sample file to any of the numerous free sites that allow file uploads and then provide the link to the file. I normally use this site, but there are plenty other sites available.
I got it. Sorry for the delay. Is this good?

Additional info looker Standard Unlimited 2022-07-17T1155.csv
ABCDEFGHIJKLMNOPQRST
1Shipment IDCreated DateFinance Record DateEntity NameShipper Shipment ReferenceBOL #PO #POD File StatusPOD Uploaded DateIs TONU (Yes / No)Shipper Rate TypeStatusShipper Line Haul RateShipper Fuel RateShipper Total Accessorial RateShipper TONU RateCity, StateCity, State
211055487########Greif1.47E+08398102343_398A02457MissingNoSpote_submitted840.2300Oregon, OHBrownsburg, IN
321055450########Greif1.47E+082.49E+09MissingNoSpote_submitted710.4200Louisville, KYCobden, IL
431055311########Electrolux6551159465511594{}MissingNoContracttendered1422.1400Lancaster, TXLubbock, TX
541054982########Ravago Americas LLC109578301095783022144MissingNoBackup/Publishedassigned506.2400Ware, MAWoonsocket, RI
651055194########Peloton1804318043TO89965MissingNoContracton_hold1498314.740Linden, NJOakdale, PA
761055023########AldiCM24755417CM24755417SWN-182467,SWN-191216MissingNoSpottendered5176.040900Columbia, MOSouth Windsor, CT
871054860########Ravago Americas LLC1095764910957649{}MissingNoSpotassigned140200Manchester, TNGoshen, IN
981055190########AldiCM24760488CM24760488HAI-188604MissingNoContracton_hold3806.78964.91900Laredo, TXHaines City, FL
1091055228########Ravago Americas LLC1095825510958255MissingNoSpotoffered140200Manchester, TNGoshen, IN
Additional info looker Standard
 
Upvote 0
You can't upload files to this site. You can upload the sample file to any of the numerous free sites that allow file uploads and then provide the link to the file. I normally use this site, but there are plenty other sites available.
Or this is better?
 
Upvote 0
That link is to a .xlsx file, not a .csv file.

Part of your question was to open the .csv file for you, so we need to see a small sample of the .csv file in .csv form, not .xlsx.
 
Upvote 0
That link is to a .xlsx file, not a .csv file.

Part of your question was to open the .csv file for you, so we need to see a small sample of the .csv file in .csv form, not .xlsx.
Sorry about that. Here is the actual file with 10 rows of data
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top