Save Monthly Data To New Workbook Named As Month

kiz · Apr 13th 2008

I have a spreadsheet that has 3 columns, date, id and amount for the whole year.
what I need to do is have a macro on another sheet or workbook that has an entry for month. when i enter the month, i need to get all the data for that particular month and then save it with the month name. i am not very sure how i can do this. pivot table does not work as i need to save a file each month. thanks in advance for your help.

Simon Lloyd · Apr 13th 2008

Re: Macro To Save New File By Month Name

If you had a sheet called the month name wouldn't that have all the data from that month in it?

kiz · Apr 13th 2008

Re: Macro To Save New File By Month Name

thanks. i want the macro to save at the end of each month, a new file with the data for that month. for example, in cell a1 i put the month, say Jan. then all the data is filled in and then with a save button, save a new file with month plus file name. thanks

Simon Lloyd · Apr 13th 2008

Re: Macro To Save New File By Month Name

You can assign thsi macro to a button, you need to change the destination path of the saved workbook, the macro will prompt you for a month enter one press ok and a new workbook will be saved to your designated path with a sheet for that month named as the original workbook plus the month!

Dont forget to change

Quote

"C:\Documents and Settings\USER\My Documents\

for your actual path that you want to save it to!

Code

Sub copy_month_data_new_workbook()
Dim Rng As Range, MyCell As Range
Dim IB As String
Dim aWorkbook As String
aWorkbook = ThisWorkbook.Name
IB = InputBox("Enter the month you want to copy!", "Month Selector")
Application.ScreenUpdating = False
Set Rng = Sheets("Sheet1").Range("A2:A" & Range("A65536").End(xlUp).Row)
Sheets.Add
ActiveSheet.Name = IB
For Each MyCell In Rng
If Application.WorksheetFunction.Text(MyCell, "mmmm") = IB Then
MyCell.EntireRow.Copy Destination:=Sheets(IB).Range("A65536").End(xlUp).Offset(1, 0)
End If
Next
Sheets(IB).Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\USER\My Documents\" & aWorkbook & " - " _
        & IB & ".xls", FileFormat:=xlNormal, ReadOnlyRecommended:=True
        ActiveWorkbook.Close
Application.DisplayAlerts = False
Sheets(IB).Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Display More

kiz · Apr 13th 2008

Re: Macro To Save New File By Month Name

thanks for your help. this is great, however i don't want an InputBox to enter month. it should copy the month from cell a1. and file named each month as fileName_Month.xls
thanks so much.

Dave Hawley · Apr 13th 2008

Re: Macro To Save New File By Month Name

Quote

pivot table does not work as i need to save a file each month. thanks in advance for your help.

PivotTable would be my preference with the date field grouped by Month.

You show only the Month wanted, Save As and convert to PivotTable to static values via Paste Special - Values and then Move the sheet to a new Workbook.

OR

Use Drill Down and the wanted Month

OR

Use Show Pages...

kiz · Apr 13th 2008

Re: Save Monthly Data To New Workbook Named As Month

thanks but not sure what drill down or use show pages is.
and pivot will not work for me as entries are made several times during the day by many users. i know how i can get the data but was not sure how i could get a macro to save a file named to whatever is in cell a1.

thanks again for all the help.

Dave Hawley · Apr 13th 2008

Re: Save Monthly Data To New Workbook Named As Month

Ok, so you have made up your mind without knowing all the facts or details of PivotTables. Ho hum

Simon Lloyd · Apr 13th 2008

Re: Save Monthly Data To New Workbook Named As Month

Just change IB for

Code

Range("A1").Value

, Kiz just a word....Dave is right, excel's built in functions are far more efficient and useable, to change destination or what it copies and how in code takes a more time and knowledge, than letting excel do the work for you, take a few minutes to follow daves suggestions to find out how powerful pivot tables are!

kiz · Apr 13th 2008

Re: Save Monthly Data To New Workbook Named As Month

Thanks, i know you both are right about pivot tables, however it will not work here. i guess i was not too clear on what i need. the code above is greatly appreciated, but changing the IB input, i guess i had to change more as now i get a error. thanks again.

Simon Lloyd · Apr 13th 2008

Re: Save Monthly Data To New Workbook Named As Month

This works perfect for me:

Code

Sub copy_month_data_new_workbook()
    Dim Rng As Range, MyCell As Range
    Dim aWorkbook As String
    Dim IB As String
    aWorkbook = ThisWorkbook.Name
    IB = Sheets("Sheet1").Range("A1").Value
    Application.ScreenUpdating = False
    Set Rng = Sheets("Sheet1").Range("A2:A" & Range("A65536").End(xlUp).Row)
    Sheets.Add
    ActiveSheet.Name = IB
    For Each MyCell In Rng
        If Application.WorksheetFunction.Text(MyCell, "mmmm") = IB Then
            MyCell.EntireRow.Copy Destination:=Sheets(IB).Range("A65536").End(xlUp).Offset(1, 0)
        End If
    Next
    Sheets(IB).Copy
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\USER\My Documents\" & aWorkbook & " - " _
    & IB & ".xls", FileFormat:=xlNormal, ReadOnlyRecommended:=True
    ActiveWorkbook.Close
    Application.DisplayAlerts = False
    Sheets(IB).Delete
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Display More

don't forget to change the path, and remember that this is looking for the full month name not Jan but January, if you want to use Jan then remove one of the m's from

Code

If Application.WorksheetFunction.Text(MyCell, "mmmm") = IB Then

Save Monthly Data To New Workbook Named As Month

Participate now!

Share