How to run all variable options on macro derived from master list Hi all, This i

Question

How to run all variable options on macro derived from master list

Hi all,

This is the master list for my macro. Each heading has a drop down list. There is 19 different mySaveAsFolder types. Each mySaveAsFolder type has a dependant mySaveAsName list with approx. 10 different options. I need my macro to also have the option to run all my variables (ie. run for RIDGE and all files in the ridge folder - 1111, 2222, 3333 so on and then repeat the process for the next folder. How would i go about doing this? Appreciate any help in advance. Thanks, Tatjana

My macro code is -

'==========>>
Option Explicit

'---------->>
Public Sub PassVariables()
     Dim WB As Workbook
     Dim SH As Worksheet

     Set WB = ThisWorkbook
     Set SH = WB.Sheets("Sheet1")

     With SH
      Call Main(myYear:=.Range("A2").Value, _
                myQuarter:=CStr(.Range("B2").Value), _
                myFolder:=CStr(.Range("C2").Value), _
                mySaveAsFolder:=CStr(.Range("D2").Value), _
                mySaveAsName:=CStr(.Range("E2").Value), _
                blCreateFolder:=CStr(.Range("F2").Value))
     End With
End Sub '---------->>
Public Sub Main(myYear As Variant, myQuarter As String, _
                 myFolder As String, _
                 mySaveAsFolder As String, _
                 mySaveAsName As String, _
                 blCreateFolder As String)
     Dim WB As Workbook
     Dim WS As Worksheet
     Dim spath As String
     Dim sSaveAsPath As String
     Dim sFilename As String
     Dim sFullname As String
     Dim aStr As String

     aStr = myQuarter & " " & myYear
     spath = "X:specific folder" _
     & myYear & "" & aStr & "TMT" & myFolder

     sSaveAsPath = "X:specific folder" & myYear & "" _
                   & aStr & "TMT" _
                   & mySaveAsFolder

     sFilename = "ST " & aStr & ".xlsm"
     sFullname = spath & "" & sFilename

     ChDir spath
     Workbooks.Open Filename:=sFullname, Updatelinks:=0
     Set WS = ActiveSheet
     Set WB = Workbooks.Add(xlWBATWorksheet)
     WS.Range("A1:S84").Copy
     WB.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
     Application.CutCopyMode = False

     If blCreateFolder Then
         MkDir sSaveAsPath
     End If

     ChDir sSaveAsPath
     ActiveWorkbook.SaveAs Filename:=sSaveAsPath & "" & mySaveAsName, _
                           FileFormat:=xlOpenXMLWorkbook, _
                           CreateBackup:=False
End Sub
'<<==========   

Explanation / Answer

If I understood it correctly, you will have to place your block of code in a loop:

For i = 2 to 100

'say there are 100 file inputs in your master file

'your code block with the below changes:

With SH
      Call Main(myYear:=.Range("A" & i).Value, _
                myQuarter:=CStr(.Range("B" & i).Value), _
                myFolder:=CStr(.Range("C" & i).Value), _
                mySaveAsFolder:=CStr(.Range("D" & i).Value), _
                mySaveAsName:=CStr(.Range("E" & i).Value), _
                blCreateFolder:=CStr(.Range("F" & i).Value))
     End With

Next

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.