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
 
Replace the code you have for the button page, most likely sheet1 module, with the following code:

VBA Code:
Sub LoadCSV_FileToSheet()   ' Version 2
'
    Dim StartTime           As Double
    StartTime = Timer                                                                           ' Start the stopwatch
'
    Dim LastRow             As Long, StartRow           As Long
    Dim CSV_FileToOpen      As String
    Dim DestinationPath     As String
    Dim HeadersToAddArray   As Variant
    Dim wsDestination       As Worksheet
'
    DestinationPath = Environ("USERPROFILE") & "\Desktop\" & "Standard Aging Local\"            ' <--- Set this to the path of the DestinationFile
'                                                                                               '       Partitioned_CSV_FileArray to
    StartRow = 2                                                                                ' <--- Set this to the start row of data
    HeadersToAddArray = Array("Stark Receivable Total", "Rounded Fuel Rate")                    ' <--- Set extra headers to add to T:U of wsDestination
'
    CSV_FileToOpen = Dir(Environ("USERPROFILE") & "\Downloads\" & _
            "Additional info looker Standard Unlimited*.csv")                                   ' Save found CSV file to CSV_FileToOpen
'
'-----------------------------------------------------------------------------------------------
'
    Workbooks.Open Filename:=Environ("USERPROFILE") & "\Downloads\" & CSV_FileToOpen            ' Open the CSV file
'
    ActiveSheet.Name = "Additional Info Lookup"                                                 ' <--- Set this to the new sheet name of the CSV file
    Set wsDestination = Sheets(ActiveSheet.Name)                                                ' Save the sheet name to wsDestination
'
    Application.ScreenUpdating = False                                                          ' Turn ScreenUpdating off
'
    With wsDestination
        .Range("T1:U1").Value = HeadersToAddArray                                               '   Add headers to DestinationSheet
'
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row                                       '   Get last used row in Column B of destination sheet
'
        .Range("T" & StartRow & ":T" & LastRow).FormulaR1C1 = "=RC[-6]+RC[-4]+RC[1]+RC[-3]"     '   Load Formulas to Column T
        .Range("U" & StartRow & ":U" & LastRow).FormulaR1C1 = "=ROUND(RC[-6],2)"                '   Load Formulas to Column U
'
        .Range("T" & StartRow & ":U" & LastRow).Copy                                            '   Copy formula range into memory (Clipboard)
        .Range("T" & StartRow & ":U" & LastRow).PasteSpecial xlPasteValues                      '   Paste just the values back to range
        Application.CutCopyMode = False                                                         '   Clear clipboard & 'marching ants' around copied range
'
        .UsedRange.EntireColumn.AutoFit                                                         '   Autofit used columns
    End With
'
    Application.ScreenUpdating = True                                                           ' Turn ScreenUpdating back on
'
    ChDir DestinationPath                                                                       ' Change the default directory
'
    Application.DisplayAlerts = False                                                           ' Turn DisplayAlerts off to block 'Overwrite file' message
    ActiveWorkbook.SaveAs Filename:= _
        DestinationPath & "Additional Info Lookup Data.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False   ' Save CSV file as XLSX file
    Application.DisplayAlerts = True                                                            ' Turn DisplayAlerts back on
'
    Debug.Print LastRow & " CSV rows processed."
    Debug.Print "Time to complete = " & Timer - StartTime & " seconds."                         ' Display the Time to complete CSV processing
'                                                                                               '       to the 'Immediate Window'(CTRL-G)
    MsgBox "CSV Processing Completed."                                                          ' Notify user that script has finished
End Sub

Run that on your full size CSV file and then paste here the results from the 'Immediate' window. It will show the # of CSV rows processed as well as the time it took to complete.
It worked! But the immediate window that popped up only showed this
1658184853116.png

Not the # of rows or time. But it did get all the rows and it took like 3 seconds. But it worked!!!!!!
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
In the VB editor (VBE) window, the window that shows the yellow line, like in post #55, hold down the 'CTRL' key & tap the 'G' key. The 'Immediate' window should appear on the screen.
 
Upvote 0
If you look through the code, you may notice that I didn't exactly do what you asked for in the original post #1, but the end result is the same.

Instead of opening the destination file and copying the csv data over to it, I just open the csv file, rename the sheet name, add the formulas to it & then save the result to the destination folder as a xlsx file & the name of the result file that you wanted, thus overwriting the original destination file. End result is the same, just different method to accomplish what you asked for.
 
Upvote 0
If you look through the code, you may notice that I didn't exactly do what you asked for in the original post #1, but the end result is the same.

Instead of opening the destination file and copying the csv data over to it, I just open the csv file, rename the sheet name, add the formulas to it & then save the result to the destination folder as a xlsx file & the name of the result file that you wanted, thus overwriting the original destination file. End result is the same, just different method to accomplish what you asked for.
It's perfect. Same result. My main template still grabs the data from there. Thank you!

Quick question. Sorry to bother you.
How would you change this formula:
FormulaR1C1 = "=RC[-6]+RC[-4]+RC[1]+RC[-3]"

If I wanted this:
=COUNTIFS(B:B,B2, E:E, ">0")

It's for another database just like the one you just helped me with. But since you helped me with this one, all I have to do is change a few things to make it work with the other one. The only thing I don't know is how I would make the above formula into a VBA

.Range("H" & StartRow & ":H" & LastRow).Formula... <---

1658187269611.png
 
Upvote 0

Forum statistics

Threads
1,223,101
Messages
6,170,116
Members
452,302
Latest member
TaMere

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