Using variables

[travisjamespeterson]

In my vba code below, I specify a year in to do the work I need. My problem is that, in my code I have to enter the year multiple times for the code to work propertyl. I was wondering if there is a way I could create a varaible in the beginnning so I only have to change the year one time when I want to run the code.

Sub buildmto()
'
' buildmto Macro
'Add the required data worksheets

Worksheets.Add().Name = "1998" 'ENTER DATE HERE TO CREATE WORKSHEED


Excel.Application.Worksheets("Perez_3").Select 'Make the source data table active.
Range("A11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter

'Autofilter for a specified name, specified in the worksheets above.
ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:="1998"
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("1998").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Save CSV file to location as the source .xls
Dim oldname$, newname$, oldpath$, oldformat
Application.DisplayAlerts = False 'avoid safetey alert

With ActiveWorkbook
oldname = .Name
oldpath = .Path
oldformat = .FileFormat

newname = .ActiveSheet.Name

.ActiveSheet.SaveAs _
Filename:=oldpath + "\" + newname, FileFormat:=xlCSV
.SaveAs Filename:=oldpath + "\" + oldname, FileFormat:=oldformat

End With

Application.DisplayAlerts = True

'Active the original worksheet and turn autofilter off
Excel.Application.Worksheets("Perez_3").Select
Worksheets("Perez_3").AutoFilterMode = False

End Sub

[Marcol]

Hi travisjamespeterson

Welcome to the Forum

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Then try this

Please Login or Register  to view this content.

If we had a sample workbook showing Before and After, we could probably shorten your code a bit.

The sample should clearly illustrate your problem and not contain any sensitive data.

Cheers