Creating a macro with variables

[Macro_]

Hi all, I currently have a macro that updates/saves a number of different data sets for a specific period. I need to be able to make it be able to update the data sets for various different periods.. how would I go about doing this? Appreciate any help whatsoever! Thank you in advance for any tips!

 

[Andrew Poulsom]

To get an answer you will need to post your current macro and specify how you want it changed.

 

[par60056]

Macros that can be run cannot have parameters.

You could make a function which can then have parameters but this means putting the function call in a cell with the parameters.

What I do is all of my master workbooks have a sheet named "Parameters" The values are filled in before the macro is run and the first thing it does is pull the values from the parameters sheet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Fishheads[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub MyMacro()
Dim theYear As Integer
Dim theMonth As Integer
Dim theClient As String


theYear = Sheets("Parameters").Cells(1, 2)
theMonth = Sheets("Parameters").Cells(2, 2)
theClient = Sheets("Parameters").Cells(3, 2)


…
End Sub

 

[Macro_]

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!!

[COLOR=#222222]
Sub Macro3()
'
' Macro3 Macro
'
'
    ChDir _
        "X:\specific folder\2014\Q4 2014\TMT\TST"
    Workbooks.Open Filename:= _
        "X:\specific folder\2014\Q4 2014\TMT\TST\ST 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\2014\Q4 2014\TMT\test"
    ActiveWorkbook.SaveAs Filename:= _
        "X:\specificfolder\2014\Q4 2014\TMT\test\1111", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ChDir _
        "X:\specific folder\2014\Q4 2014\TMT\TST"
    Workbooks.Open Filename:= _
        "X:\speciic folder\2014\Q4 2014\TMT\TST\ST 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\2014\Q4 2014\TMT\test"
    ActiveWorkbook.SaveAs Filename:= _
        "X:\Fspecific folder\2014\Q4 2014\TMT\test\2222", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub[/COLOR]

 

[par60056]

Several comments about your macro, then an answer to the question.

1) since you fully qualify the file name on the open, you don't need to CHDIR.
2) Your macro works based on the active cell when you open the document. This could be anywhere. I would not trust the active cell to be the right place.

You could use InputBox to ask for the information. Something like :

Dim theYear As String
Dim theQtr As String


theYear = InputBox("Enter Year", "Period")
theQtr = InputBox("Enter Quarter", "Period")
Workbooks.Open Filename:= _
        "X:\specific folder\" & theYear & "\Q" & thequarter & " " & theYear & "\TMT\TST\ST Q" & thequarter & " " & theYear & ".xlsm" _
        , UpdateLinks:=0

You would probably want to check for valid input and add error trapping or use other methods to check for the file existing.