PDA

View Full Version : Solved: Create duplicate workbooks based on list



Sir Babydum GBE
07-31-2007, 09:57 AM
One last question (for now - I think)

Based on a list of names on the sheet "Agents" I want to run a macro that will cause the workbook to duplicate itself as many times as there are names on the list, and to call each new workbook by the name and tomorrow's date (yyyy mm dd) (e.g. filename for me might be "2007 08 01 Sir BD").

Actually - Ideally I'd like to choose the date of the new batch of workbooks in case I want to create Monday's batch on a Friday - for example - but no big deal if not possible.

If you can help or point me in the right direction, I'd be most grateful.

Sir BD

Bob Phillips
07-31-2007, 10:50 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim mpNextDate As Variant

With Worksheets("Agents")

mpNextDate = InputBox("What date to use?")
If Not mpNextDate = "" Then
If Not IsDate(mpNextDate) Then

MsgBox "Invalid input"
Else

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
ThisWorkbook.SaveAs Filename:=Format(mpNextDate, "yyyy mm dd ") & _
.Cells(i, TEST_COLUMN).Value
Next i
End If
End If
End With

End Sub

Sir Babydum GBE
08-01-2007, 01:21 AM
I think I love you

Sir Babydum GBE
08-01-2007, 01:57 AM
Ok, I'm really trying to figure this out for myself - honestly...

Currently the attached code is causing the copied workbooks to be saved into the My Documents folder - whereas I want the docs to be copied to the same folder as the parent document. I don't want to specify the file path in the code because it will change each day.

I looked up calling the SaveAs dialog box - but I only want to specify a location (and only once) rather than specify a filename - seeing as that job is already being automated by the macro.

Any ideas

Sir BD

Bob Phillips
08-01-2007, 02:34 AM
Just preced the filename in the save with

ThisWorkbook.Path & "\"

and no need for an interrupt of any kind.

Bob Phillips
08-01-2007, 02:35 AM
To be a bit more robust, use

ThisWorkbook.Path & Application.PathSeparator

Sir Babydum GBE
08-01-2007, 07:00 AM
To be a bit more robust, use

ThisWorkbook.Path & Application.PathSeparator

That works perfectly. You're the man!