Macro to Copy Data from a Sheet and Save it as a different Sheet with a Specified File Nam

Hi all,

Hi all, I'm facing a problem and need some help to sort it out. Here is the problem.

I'm using an Excel Sheet from which I need to export the data on a Monthly Basis at the end of every month. This file will be saved as Month_Year (ex: Sep 2013, Nov 2013 e.t.c). I want to get the Month and Year get updated automatically. So I'm Using NOW() function of Excel in 2 cells one for Month and the other for Year. But when I'm trying to run the macro its giving an error, the data is being exported but the file is not being saved in the specific file format. I'm posting the code and Sample workbook for getting a clear understanding of the problem.

Please help me in sorting it out.. Thanks in Advance..

Sub Report_Transfer()

Dim wshSource As Worksheet

Set wshSource = ActiveSheet


    ActiveSheet.Copy
    With ActiveSheet.UsedRange
        .Copy
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:=Range("D5") & "_" & Range("D4") & ".xls"
  
End Sub

Since you are using current date for the filename, just change to this:

ActiveWorkbook.SaveAs Filename:=Format(Now, "MMM") & "_" & Format(Now, "YYYY") & ".xls"

Thank u very much Millz..
It worked..

Can u plz let me know if it is possible to save the file in a specific location in the same format..

Yes, you just have to specify the directory before the filename.
E.g.

ActiveWorkbook.SaveAs Filename:="C:\Temp\" & Format(Now, "MMM") & "_" & Format(Now, "YYYY") & ".xls"

Hi Millz,

Thank u for the reply...

Rep Added