How to run all variable on a macro

[Macro_]

Hi all, I have the current code set up with variable options on a masterlist. How do I make the macro to run for all variables? Thanks In advance for help, macro_ -

'==========>>
 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
 '<<==========

 

[mrmmickle1]

You need to declare Public Variables. Look at this link for reference:

Different Variable Scopes

 

[Macro_]

Looks exactly what I need to. I am a beginner with macro and am not sure how I would incorporate this with my code? Thanks for your help mrmmickle1

 

[Jerry Sullivan]

Hi all, I have the current code set up with variable options on a masterlist. How do I make the macro to run for all variables?

Could you explain this in more detail?

I'm guessing that you have one set of "variable options" in Range A2:F2, then another on A3:F3, and so on....

If that's correct, I don't think there's any need to use Public Variables.

 

[Macro_]

myYear	myQuarter	myFolder	mySaveAsFolder	mySaveAsName	blCreateFolder
2014	Q4	TST	TEST	1111	TRUE

<colgroup><col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="339" style="width: 254pt; mso-width-source: userset; mso-width-alt: 12397;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <tbody>
</tbody>

 

[Macro_]

My macro list is above. 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, macro_

 

[Macro_]

Jerry Sullivan, thank you. I have updated it so I have different sets of variable options. My only issue now is that I have an error coming up in the code at

If blCreateFolder Then
         MkDir sSaveAsPath
     End If

as after the first folder has been created its already there for the other files needing to go into that folder...how do I fix this? Thanks! Macro_

 

[Jerry Sullivan]

Macro_,

Here is an example of how to test for the existence of a folder before trying to create it....

Sub Test()
 Dim sSaveAsPath As String
 sSaveAsPath = "C:\TEST"
 
'--Make directory only if it doesn't already exist
 If Dir(sSaveAsPath, vbDirectory) = "" Then
   MkDir sSaveAsPath
 End If

End Sub

 

[Macro_]

Hello, thank you MrExcel MVP, I have fixed that. I now have the issue of every time a new sheet is run a dialogue box comes up saying '.... template is already open. Are you sure to want to open.' and I have to manually click yes for all. How do I make my macro say yes to all and/or ignore the dialogue box? This is my code

'==========>>
 Option Explicit
 '---------->>
 Public Sub PassVariables()
      Dim WB As Workbook
      Dim SH As Worksheet
      Set WB = ThisWorkbook
      Set SH = WB.Sheets("Sheet1")
      Dim i As Variant
      For i = 2 To 151
      
     With SH
      Call Main(myYear:=.Range("A2").Value, _
                myQuarter:=CStr(.Range("B2").Value), _
                myFolder:=CStr(.Range("C2").Value), _
                mySaveAsFolder:=CStr(.Range("D" & i).Value), _
                mySaveAsName:=CStr(.Range("E" & i).Value), _
                blCreateFolder:=CStr(.Range("F" & i).Value))
     End With
Next
 
End Sub '---------->>

'---------->>
Public Sub Main(myYear As Variant, myQuarter As String, _
                   myFolder As String, _
                   mySaveAsFolder As String, _
                   mySaveAsName As String, _
                   Optional blCreateFolder As Boolean)
     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:\SPECIFICFOLDER\" & myYear & "\" & aStr & "\TMT\" & myFolder
     sSaveAsPath = "X:\SPECIFICFOLDER\" & myYear & "\" & aStr & "\TMT\" & mySaveAsFolder
     sFilename = "ST" & aStr & ".xlsm"
     sFullname = spath & "\" & sFilename
     Workbooks.Open Filename:=sFullname, UpdateLinks:=0
     ActiveCell.Offset(-1, 0).FormulaR1C1 = mySaveAsName
     Set WS = ActiveSheet
     Set WB = Workbooks.Add(xlWBATWorksheet)
     WS.Range("A1:S84").Copy
     WB.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
     WB.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
     Application.CutCopyMode = False
     
     If blCreateFolder Then
          MkDir sSaveAsPath
         blCreateFolder = False
     End If

     'ChDir sSaveAsPath
    With ActiveWorkbook
         .SaveAs Filename:=sSaveAsPath & "\" & mySaveAsName, _
                 FileFormat:=xlOpenXMLWorkbook, _
                 CreateBackup:=False
        .Close SaveChanges:=False
    End With

 End Sub
 '<<==========

Thanks, macro_