How to create a dynamic name for various workbooks?

erifdlrow

New Member
Joined
Jun 18, 2022
Messages
2
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,708
You could use the Dir function to loop through every .xlsx file in the "C:\Desktop\InVideo\Automated Process Macro\" folder, like this:

VBA Code:
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.
 
Upvote 0
Solution

erifdlrow

New Member
Joined
Jun 18, 2022
Messages
2
Platform
  1. Windows
You could use the Dir function to loop through every .xlsx file in the "C:\Desktop\InVideo\Automated Process Macro\" folder, like this:

VBA Code:
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!!
 
Upvote 0

Forum statistics

Threads
1,186,441
Messages
5,957,872
Members
438,329
Latest member
Aimon Rauf

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
Top