How to create multiple files?

[manbearpig]

Dear All, I have a following dilemma:

I need to create multiple files from a single file while automatically changing one cell.

the file is called "Property1 20100931"

Sheet1!J3 contains date let's say 9/31/2010

I need to create 30 files with decreasing date i.e.

changing DATE in Sheet1!J3 to 9/30/2010 and changing FILE NAME to "Property1 20100930" ..... all the way to 9/1/2010

All of these files I want to save in a single folder.


I would greatly appreciate any thoughts!

Thank you!

 

[ChrisOswald]

Hi,

Try this macro

Sub MakeABunchOfNearDuplicateFiles()
    Dim Wb             As Workbook
    Dim MyDate         As Date
    Dim MyName         As String
    Dim MyNewName      As String
    Dim dayCounter     As Long
    Set Wb = ActiveWorkbook
    MyName = Wb.Name
    MyDate = Wb.Worksheets("Sheet1").Cells(3, 10)
    dayCounter = Day(MyDate)
    Do
        dayCounter = dayCounter - 1
        If dayCounter = 0 Then Exit Do
        Wb.Worksheets("Sheet1").Cells(3, 10) = Wb.Worksheets("Sheet1").Cells(3, 10) - 1
        Wb.SaveAs ("C:\The Save Folder\" & Left(MyName, 10) & Year(MyDate) _
                   & Right("0" & Month(MyDate), 2) & Right("0" & dayCounter, 2))
    Loop
 
End Sub

you'll need to change the "C:\The Save Folder\" to the correct path.
The macro also doesn't make 30 new books: it makes a book for every day down to the first day of the month.

 

[manbearpig]

THANK YOU SO MUCH!!!!

it works great!