Error in code with variable option

Macro_

New Member
Joined
Sep 3, 2014
Messages
35
Hi all, I am trying to create a variable option with my macro and currently have the following code. I have an error coming up at the beginning saying -sub or function not defined- Please help..
Rich (BB code):
'==========>>
 Option Explicit

 '---------->>
 Sub PassVariables()

     Call Tester(myYear:=2014, _
                 myQuarter:="Q4", _
                 myFolder:="TST", _
                 mySaveAsFolder:="Test", _
                 mySaveAsName:="1111", _
                 mySaveAsName2:="2222")
 End Sub

 '---------->>
 Public Sub Main(myYear As Variant, myQuarter As String, _
                   myFolder As String, _
                   mySaveAsFolder As String, _
                   mySaveAsName As String, _
                   mySaveAsName2 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
     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
     Application.CutCopyMode = False
     ChDir sSaveAsPath
     ActiveWorkbook.SaveAs Filename:=sSaveAsPath & "\" & mySaveAsName, _
                           FileFormat:=xlOpenXMLWorkbook, _
                           CreateBackup:=False
     ChDir spath
     Workbooks.Open Filename:=sFullname, Updatelinks:=0
     ActiveCell.Offset(-1, 0).FormulaR1C1 = mySaveAsName2
     Set WS = ActiveSheet
     Set WB = Workbooks.Add(xlWBATWorksheet)
     WS.Range("A1:S84").Copy
     WB.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
     Application.CutCopyMode = False
     ChDir sSaveAsPath
     ActiveWorkbook.SaveAs Filename:=sSaveAsPath & "\" & mySaveAsName2, _
                           FileFormat:=xlOpenXMLWorkbook, _
                           CreateBackup:=False
 End Sub
 '<<==========
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi Macro_,

You're asking the code to call a macro named Tester that doesn't exist. Change Tester to Main and all should be good.

HTH

Robert
 
Upvote 0
Thanks Robert! That worked as now I don't have errors. However, when I click run macro the dialogue box comes up with a list of different macros. Do I need to create a new name for this macro or click onto another one where I have previously created to run the function? Cheers
 
Upvote 0
Hi Macro_,

Glad to hear we're on the right track.

I'm not too sure what you mean as the Main macro will not appear on the Macro dialog as it needs parameters for it run. This is by design. The PassVariables will appear though.

Robert
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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
Back
Top