Macro to create files based on month/year

I have a macro that pastes some data from an updated workbook into an
archive workbook. My problem is that 32 rows of data (5 columns wide)
are pasted to the archive every half hour and this will continue 24hrs
a day for months (worried about reaching the 65,536 rows limit). Is
there a way I can have the macro make a new file at the start of each
month? For example, the first file would be Archive_11-05.xls and the
data would be pasted there for all of november. Then when December
starts, a new file Archive_12-05.xls, would be created and data would
be pasted there for a month. This way, the files don't get too big and
if I need to go back and look at the data, it is sorted by month. Right
now I just have one file and I need to be able to leave this macro
unattended and archiving data for a long time (possibly years!) One
other solution that is acceptable would be for Excel to look at what
line it is pasting to and when it gets to, say, line 10000 it will
create a new file to paste in. I'm not tied to using the date as a file
name but it would be nice. Thanks for any help you can give.

Untested in Excel. Works but, I don't claim to be a great code writer. I
stand way behind the things I give away<bg>


Const sFilePath = {your top level folder path}
Const sStartFName = {first part of your files name} ' eg 'Expenditure'
On Error Resume Next ' causes no break when path found
sFilePath = sFilePath & "\" & Format(Date, "YYYY")
mkdir sFilePath
sFilePath = sFilePath & "\" & Format(Date, "MMMM")
mkdir sFilePath

sYear = Format(Date, "YYYY")
sMonth = Format(Date, "MMM")
sDay = Format(Date, "DD")
sFullFileName = sFilePath & "\" & sStartFName & sYear & "_" & sMonth & "_"
& sDay & ".doc"
FileExists = True
If Dir(sFullFileName) = "" Then FileExists = False

myFileExists = FileExists







"Jon" <[email protected]> wrote in message
news:[email protected]...
>I have a macro that pastes some data from an updated workbook into an
> archive workbook. My problem is that 32 rows of data (5 columns wide)
> are pasted to the archive every half hour and this will continue 24hrs
> a day for months (worried about reaching the 65,536 rows limit). Is
> there a way I can have the macro make a new file at the start of each
> month? For example, the first file would be Archive_11-05.xls and the
> data would be pasted there for all of november. Then when December
> starts, a new file Archive_12-05.xls, would be created and data would
> be pasted there for a month. This way, the files don't get too big and
> if I need to go back and look at the data, it is sorted by month. Right
> now I just have one file and I need to be able to leave this macro
> unattended and archiving data for a long time (possibly years!) One
> other solution that is acceptable would be for Excel to look at what
> line it is pasting to and when it gets to, say, line 10000 it will
> create a new file to paste in. I'm not tied to using the date as a file
> name but it would be nice. Thanks for any help you can give.
>