How to create a dynamic name for various workbooks?

[erifdlrow]

Here is the scenario :

I have "n" Workbooks which get filtered and their data is copied to a "Master Excel"

I have created the whole process for a single workbook named "1".

But in order to replicate that process for the other workbooks (named 2,3,4....so on), I have to copy-paste the whole script and change the names from Workbooks("1.xlsx") to Workbooks(" 2.xlsx"), Workbooks(" 3.xlsx") etc..

This seems quite inefficient

I am looking for a way to have a dynamic value for the Workbook name.

How should I go about this?

Here's the code of what I have done

Sub Openwscopy()

Workbooks.Open "C:\Desktop\InVideo\Automated Process Macro\1" ''''''''Change Workbook name here
Sheets(1).Name = "Sheet1"

End Sub


Sub AhrefsFilters()
'
' AhrefsFilters Macro
'

'


Workbooks("1").Worksheets("Sheet1").Columns("A:A").Select  '''''''Change Workbook name here
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "INOUT"
    Range("F2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]"
    Range("F2").Select
    Selection.Copy
    Range("E2").Select
    Selection.End(xlDown).Select
    Range("F123").Select
    ActiveSheet.Paste
    Range(Selection, Selection.End(xlUp)).Select
    Application.CutCopyMode = False
    Selection.FillDown
    Selection.End(xlUp).Select
    Selection.End(xlToLeft).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$123").AutoFilter Field:=2, Criteria1:=">=20", _
        Operator:=xlAnd
    ActiveSheet.Range("$A$1:$F$123").AutoFilter Field:=5, Criteria1:=">=1000", _
        Operator:=xlAnd
    ActiveSheet.Range("$A$1:$F$123").AutoFilter Field:=6, Criteria1:=">=-1000" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    


End Sub
Sub CopyHereToThere()

'Declaring variables

Dim wscopy As Worksheet
Dim wsdest As Worksheet
Dim copylastrow As Long
Dim destlastrow As Long

'Set variables

Set wscopy = Workbooks("1").Worksheets("Sheet1") '''''''''''''''''''''Change Workbook name here
Set wsdest = Workbooks("Personal.XLSB").Worksheets("Sheet1")

copylastrow = wscopy.Cells(wscopy.Rows.Count, "A").End(xlUp).Row
destlastrow = wsdest.Cells(wsdest.Rows.Count, "A").End(xlUp).Offset(1).Row

'Copy from here to there

wscopy.Range("a2:a" & copylastrow).Copy _
wsdest.Range("a" & destlastrow)

End Sub
Sub Closewscopy()

'Workbooks("1").Save
Workbooks("1").Close False ''''''''Change Workbook name here

End Sub


Sub Process1()

Openwscopy
AhrefsFilters
CopyHereToThere
Closewscopy

End Sub

Sub ProcessesAll() 'Here I have done repeated the script manually which is quite unnecessary, but didn't know how to do it with looping.
Process1
Process2
Process3
Process4
Process5
Process6
Process7
Process8
Process9
Process10
End Sub

 

[John_w]

You could use the Dir function to loop through every .xlsx file in the "C:\Desktop\InVideo\Automated Process Macro\" folder, like this:

Public Sub Process_All()

    Dim file As String
  
    file = Dir("C:\Desktop\InVideo\Automated Process Macro\*.xlsx")
    While file <> vbNullString
        Workbooks.Open "C:\Desktop\InVideo\Automated Process Macro\" & file
        AhrefsFilters
        CopyHereToThere
        ActiveWorkbook.Close False
        file = Dir
    Wend
  
End Sub

and change every occurrence of Workbooks("1").Worksheets("Sheet1") to ActiveWorkbook.Worksheets(1) in the two called routines.

The Openwscopy and Closewscopy routines aren't called now so they can be deleted.

 

[erifdlrow]

You could use the Dir function to loop through every .xlsx file in the "C:\Desktop\InVideo\Automated Process Macro\" folder, like this:

Public Sub Process_All()

    Dim file As String
 
    file = Dir("C:\Desktop\InVideo\Automated Process Macro\*.xlsx")
    While file <> vbNullString
        Workbooks.Open "C:\Desktop\InVideo\Automated Process Macro\" & file
        AhrefsFilters
        CopyHereToThere
        ActiveWorkbook.Close False
        file = Dir
    Wend
 
End Sub

and change every occurrence of Workbooks("1").Worksheets("Sheet1") to ActiveWorkbook.Worksheets(1) in the two called routines.

The Openwscopy and Closewscopy routines aren't called now so they can be deleted.

Thank you so much!!!! This worked, plus I learnt something new. Again I can't thank you enough!!