VBA Automating getting data from other Files and posting into daily report-CodePudding

I'm generally new to VBA and creating automations however I've created a button which automates my daily performance reports I need to send to my team. I would love to know if there is a way to clean it up and optimize for future endeavors as its a little messy.

Through an application we store our data we have data files dumping into a server he host; my VBA code pulls from this folder based on name of file and copies a cell and posts into my host report. There are 2 main problems outside of the fact I copied this code and pasted 30 times to change which report its opening and exporting from.

Once it opens the Excel File it doesn't close it, leaving a lot of files open and unpleasing experience.
Causes lag when the processes is occurring as its spamming between sheet to sheet, would love to know if I can make the process not show.

' Sheet 14 Import

Workbooks.Open Filename:= _
    "S:\Root\Operations2\Reports\Trade Date Cash\scheduler\V14*.xls*"
Range("L7").Select
Selection.Copy
Windows("Daily Balances - Portfolio Size.xlsm").Activate
Sheets("Testing").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

' Sheet 15 Import

Workbooks.Open Filename:= _
    "S:\Root\Operations2\Reports\Trade Date Cash\scheduler\V15_*.xls*"
Range("L7").Select
Selection.Copy
Windows("Daily Balances - Portfolio Size.xlsm").Activate
Sheets("Testing").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

I have this same code 30 times for every different name going from V13-V45 and posting into cell going down F.E from 14-15 pasting into B3->B4

CodePudding user response：

If all your 30 need_to_be_opened xls workbooks are in one folder, which is: S:\Root\Operations2\Reports\Trade Date Cash\scheduler\

and all the cell to be copied in the opened workbook is always cell L7,
and the copied cell will always be paste into the first blank cell in column B,

Then you can loop to each file in that folder regardless the name of the file.

Something like this :

Sub test()
Dim p As String: Dim fn As String: Dim fso

p = "S:\Root\Operations2\Reports\Trade Date Cash\scheduler\"
Set fso = CreateObject("Scripting.FileSystemObject")

    For Each fn In fso.GetFolder(p).Files
        Workbooks.Open Filename:=fn
        ThisWorkbook.Sheets("Testing").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
                ActiveWorkbook.ActiveSheet.Range("L7").Value
        ActiveWorkbook.Close SaveChanges:=False
    Next

End Sub

The code will loop to each file in the value of p variable (the path of the folder), get the file name as fn variable, open the file, get the value of cell L7 in the activesheet of the opened workbook then put that value to whatever the first found blank row in column B, then close the workbook, then it keep on doing like that until all the workbook/file in the folder opened.

FYI, if you know for certain that the L7 value is always in the same sheet name, then it better you write the sheet name for cell L7 ---> activeworkbook.sheets("TheSheetName").range("L7").value

Also, the sub assumed that before running the sub, the condition in Testing sheet column B, the rows are all empty below cell B2 (cell B2 has value), and the name of 30 files in the folder is with increment, such as V14blablabla.xls, V15 blublublu.xls, and so on until V45_xxx.xls. So the first opened file is V14 and it write to cell B3 (now cell B4 is the first empty row), the second opened file is V15 and it write to B4, and so on.

The sub is based on seeing your code.
You need to tell us whether the value in L7 from the opened workbook will overwrite an existing value in column B sheet Testing (so, before running the sub, B3, B4, B5 etc has value) or those rows are all empty to the end of the sheet row.

The thing which I don't understand is :

posting into cell going down F.E from 14-15 pasting into B3->B4

CodePudding user response：

Application.ScreenUpdating=false
Workbooks.Open Filename:= _
            "S:\Root\Operations2\Reports\Trade Date Cash\scheduler\V14*.xls*"
Workbooks("Daily Balances - Portfolio Size.xlsm").Worksheets("Testing").Range("B3")=ActiveSheet.Range("L7").Value2
ActiveWorkbook.close SaveChanges:=False
Workbooks.Open Filename:= _
    "S:\Root\Operations2\Reports\Trade Date Cash\scheduler\V15_*.xls*"
Workbooks("Daily Balances - Portfolio Size.xlsm").Worksheets("Testing").Range("B4")=ActiveSheet.Range("L7").Value2
ActiveWorkbook.close SaveChanges:=False
Application.ScreenUpdating=true