Help with VBA auto saving workbook using existing code

[asad]

Hello Guys,

I have been using the following code for some time and is working perfect.

Sub Rotation()
'
' Macro1 Macro
' Macro recorded 9/10/2009 by Ali
'
'
    Columns("AM:AU").Select
    Selection.EntireColumn.Hidden = False
    Range("AS8:AS39").Select
    Selection.ClearContents
    Range("AL2").Select
    Selection.Copy
    Range("AL3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A8:C50").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("E8:F23").Select
    Selection.Copy
    Range("E9:F24").Select
    ActiveSheet.Paste
    Range("E24:F24").Select
    Selection.Copy
    Range("E8:F8").Select
    ActiveSheet.Paste
    Range("E24:F24").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=6
    Range("E31:F38").Select
    Selection.Copy
    Range("E32:F39").Select
    ActiveSheet.Paste
    Range("E39:F39").Select
    Selection.Copy
    Range("E31").Select
    ActiveSheet.Paste
    Range("E39:F39").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("E42:F48").Select
    Selection.Copy
    Range("E43:F49").Select
    ActiveSheet.Paste
    Range("E49:F49").Select
    Selection.Copy
    Range("E42").Select
    ActiveSheet.Paste
    Range("E49:F49").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-12
    Range("AS7").Select
    Selection.AutoFill Destination:=Range("AS7:AS38"), Type:=xlFillDefault
    Range("AS7:AS38").Select
    ActiveWindow.SmallScroll Down:=-9
    Range("G8:G38").Select
    Selection.ClearContents
    Range("G16").Select
    ActiveCell.FormulaR1C1 = "RELIEF"
    Range("G22").Select
    ActiveCell.FormulaR1C1 = "RELIEF"
    Range("A7:C7").Select
    Selection.AutoFill Destination:=Range("A7:C50"), Type:=xlFillDefault
    Range("A7:C50").Select
    ActiveWindow.SmallScroll Down:=-18
    Columns("AN:AT").Select
    Selection.EntireColumn.Hidden = True
    Range("G3").Select
    ActiveSheet.Shapes("Button 32").Select
    Range("G3").Select
    
End Sub

Now, I want to add another function. The files have a name in the format "Sandy yy-mm-dd" where "Sandy" is the constant part. I want it to auto save the file after it has done all of the above with name format "Sandy yy-mm-dd" and yy-mm-dd has to be taken from cell "AL3" in sheet named "ROSTER". The path will be - "E:\2011\Sandy yy-mm-dd".
Can this be done?
Asad

 

[Boller]

Is AL3 formatted as Date or Text ?

 

[asad]

AL3 is formatted as date in dd/mm/yy format.

 

[asad]

.

 

[asad]

Can somebody help me please???????????

 

[asad]

bump.

 

[asad]

Got it finally. It was one simple line of code that I needed. Worked it out by using portions from the different codes provided on this forum and it works sweet.
I will post it here for some body who may want to use it:

ActiveWorkbook.SaveAs Filename:="E:\2011\Sandy " & Format(Range("AL3").Value, "yy-mm-dd") & ".xls"

 

[Boller]

Here's a tidied-up version of the code you posted.

Sub RotationNew()
[AL3] = [AL2].Value
[E8:F23].Copy [E9:F24]
[E24:F24].Copy [E8:F8]
[E31:F38].Copy [E32:F39]
[E39:F39].Copy [E31:F31]
[E42:F48].Copy [E43:F49]
[E49:F49].Copy [E42:F42]
[AS8:AS39,A8:C50,E24:F24,E39:F39,E49:F49,G8:G38].ClearContents
[AS7].AutoFill Destination:=[AS7:AS38], Type:=xlFillDefault
[A7:C7].AutoFill Destination:=[A7:C50], Type:=xlFillDefault
[G16,G22] = "RELIEF"
[AN:AT].EntireColumn.Hidden = True
[G3].Select
End Sub

 

[asad]

Thanks Boller,
It is very tidy and looks good. And it does everything I needed. Thanks for your help again.
Asad