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