Creating a macro with variable options My current macro is below. It updates dat

Question

Creating a macro with variable options

My current macro is below. It updates data for different variables for the period 2014 quarter 4. I want it to be able to be flexible so for example I can type in 2015 q3 and test 5 (different folder to save this data in) and it will change all areas where it says 2014 q1 and the word test. Do you suggest creating a macro to do this or a master list.. And how would I go about doing this? thanks!!
[CODE]Sub Macro3()
'
' Macro3 Macro
'
'
    ChDir _
        "X:specific folder�4Q4 2014TMTTST"
    Workbooks.Open Filename:= _
        "X:specific folder�4Q4 2014TMTTSTST Q4 2014.xlsm" _
        , UpdateLinks:=0
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "1111"
    Dim ws As Worksheet, wb As Workbook
    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
    ChDir "X:specific folder�4Q4 2014TMT est"
    ActiveWorkbook.SaveAs Filename:= _
        "X:specificfolder�4Q4 2014TMT estI1", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ChDir _
        "X:specific folder�4Q4 2014TMTTST"
    Workbooks.Open Filename:= _
        "X:speciic folder�4Q4 2014TMTTSTST Q4 2014.xlsm" _
        , UpdateLinks:=0
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "2222"
    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
    ChDir "X:specific folder�4Q4 2014TMT est"
    ActiveWorkbook.SaveAs Filename:= _
        "X:Fspecific folder�4Q4 2014TMT est�2", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub[/CODE]   

Explanation / Answer

Your problem is my fault!

In my test workbook, the 'main' procedure was called Tester. However, when I pasted the code into my reply, on impulse I decided to change the name of the procedure to Main in order better to indicate the intention of the code. Unfortunately, I forgot to make the necessary consequential change in the PassVariables procedure!

Therefore, please accept my apologies and change:

'---------->>

Sub PassVariables()

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

End Sub

'---------->>

to

'---------->>
Sub PassVariables()
Call Main(myYear:=2014, _
myQuarter:="Q4", _
myFolder:="TST", _
mySaveAsFolder:="Test", _
mySaveAsName:="1111", _
mySaveAsName2:="2222")
End Sub

'---------->>

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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