macro with variables

[Macro_]

Hi all,
trying to create a macro with variables and have inserted the code at the beginning however haven't created a new sheet were I've listed cell variables yet.. I'm not sure wether I can create such a new workbook and if I can how to link it to this code. Also, not entirely sure if I have copied the variable section of this code correctly. Any thoughts?
Thanks in advance for help!

Sub Macro3()
'
' Macro3 Macro
'
'
    Dim lYear As Long, lQtr As Long, stText As String
    lYear = Range("Year")
    lQtr = Range("Qtr")
    
    ChDir _
        "X:\specific folder\" & lYear & "\" & lQtr & " " & lYear & "\TMT\TST"
    Workbooks.Open Filename:= _
        "X:\specific folder\2014\Q4 2014\TMT\TST\S T Q4 2014.xlsm" _
        , UpdateLinks:=0
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = ""
    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\" & lYear & "\" & lQtr & " " & lYear & "\TMT\test"
    ActiveWorkbook.SaveAs Filename:= _
        "X:\specific folder\" & lYear & "\" & lQtr & " " & lYear & "\Tmt\test\1111", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

 

[Darren Bartrup]

Hi Macro_

I haven't fully tested this, but it should work:

Sub GiveYourMacroAMeaningfulName()'
' Macro3 Macro
'
'
    Dim lYear As Long, lQtr As Long, stText As String
    Dim wrkBk As Workbook 'This will hold the reference to the current workbook.
    Dim wrkBkNew As Workbook 'This will hold the reference to the new workbook.
    Dim wrkSht As Worksheet 'This will hold the reference to the worksheet.
    
    'Where are these values coming from?  If they're in "S T Q4 2014.xlsm" they need to be placed after
    'the workbook is open.
    lYear = Range("Year")
    lQtr = Range("Qtr")
    
    Set wrkBk = Workbooks.Open(Filename:="X:\specific folder\2014\Q4 2014\TMT\TST\S T Q4 2014.xlsm", _
                               UpdateLinks:=False)
                             
    Set wrkBkNew = Workbooks.Add(xlWBATWorksheet)
    
    Set wrkSht = wrkBk.Worksheets("Sheet1") 'Or ActiveSheet
    wrkSht.Range("A1:S84").Copy Destination:=wrkBkNew.Worksheets(1).Range("A1")
    wrkBkNew.SaveAs Filename:="X:\specific folder\" & lYear & "\" & lQtr & " " & lYear & "\Tmt\test\1111\New Workbook Name.xlsm", _
        FileFormat:=xlOpenXMLWorkbook
    
End Sub