I have one workbook that I create every month and distribute to employees.
Every employee has their own version of this workbook.

Normally, I create the blank workbook and do a File-SaveAs (their operator
#/name).

Instead of doing this 30 times and saving as each persons name/number, is it
possible to key a list of their names and run a macro (or something else) to
automatically create a file with each persons name???

I can envision how I'd do this, but it would take some time to create the
code. This is what I'd do:

1) Create a workbook with the list of users ids that you want to use. I'd
probably call this aWB. Refer to the sheet with the user ids as aWS.
2) Create a macro to open the file that you want to perform the multiple
save as on. Use something like this
http://www.mrexcel.com/td0009.html
I'd probably refer to this workbook as oWB
3) Extract the user names from that worksheet. I'd put the first name in
row 2 column 1. I'd use something like

for i = 2 to aws.cells(rows.count,1).end(xlup)
fname = aws.cells(i,1).value
next i

4) WIthin the for/next loop, do the following

oWB.SaveAs Filename: fname

5) You could probably automate the emailing, but I've not done that in a
bit so am not sure where to start.

"KDP" wrote:

I have one workbook that I create every month and distribute to employees.
Every employee has their own version of this workbook.

Normally, I create the blank workbook and do a File-SaveAs (their operator
#/name).

Instead of doing this 30 times and saving as each persons name/number, is it
possible to key a list of their names and run a macro (or something else) to
automatically create a file with each persons name???

Just a typo and an alternate suggestion...

for i = 2 to aws.cells(rows.count,1).end(xlup)
should be:
for i = 2 to aws.cells(rows.count,1).end(xlup).row

And instead of using .saveas, how about .savecopyas?
Then the original workbook is undisturbed.

Barb Reinhardt wrote:

I can envision how I'd do this, but it would take some time to create the
code. This is what I'd do:

1) Create a workbook with the list of users ids that you want to use. I'd
probably call this aWB. Refer to the sheet with the user ids as aWS.
2) Create a macro to open the file that you want to perform the multiple
save as on. Use something like this
http://www.mrexcel.com/td0009.html
I'd probably refer to this workbook as oWB
3) Extract the user names from that worksheet. I'd put the first name in
row 2 column 1. I'd use something like

for i = 2 to aws.cells(rows.count,1).end(xlup)
fname = aws.cells(i,1).value
next i

4) WIthin the for/next loop, do the following

oWB.SaveAs Filename: fname

5) You could probably automate the emailing, but I've not done that in a
bit so am not sure where to start.

"KDP" wrote:

I have one workbook that I create every month and distribute to employees.
Every employee has their own version of this workbook.

Normally, I create the blank workbook and do a File-SaveAs (their operator
#/name).

Instead of doing this 30 times and saving as each persons name/number, is it
possible to key a list of their names and run a macro (or something else) to
automatically create a file with each persons name???


--

Dave Peterson

Barb - I've never done this before, but this is what I've got. When I run the
macro, it gives me the message:

Compile Error:

Expected Sub, Function, or Property

and the fname at the end is highlighted...


------------------------------------------------------

Sub Open1()
'
' Open1 Macro
' Macro recorded 3/29/2007 by b39769
'

'
Workbooks.Open Filename:="H:\oWb.xls"

For i = 2 To aws.Cells(Rows.Count, 1).End(xlUp).Row
fname = aws.Cells(i, 1).Value
Next i

oWB.SaveCopyAs Filename: fname

End Sub

-----------------------------------------------------


i have hardly any idea what i'm doing...i've gotta get a book or something
on this.

THANKS!


"Barb Reinhardt" wrote:

I can envision how I'd do this, but it would take some time to create the
code. This is what I'd do:

1) Create a workbook with the list of users ids that you want to use. I'd
probably call this aWB. Refer to the sheet with the user ids as aWS.
2) Create a macro to open the file that you want to perform the multiple
save as on. Use something like this
http://www.mrexcel.com/td0009.html
I'd probably refer to this workbook as oWB
3) Extract the user names from that worksheet. I'd put the first name in
row 2 column 1. I'd use something like

for i = 2 to aws.cells(rows.count,1).end(xlup)
fname = aws.cells(i,1).value
next i

4) WIthin the for/next loop, do the following

oWB.SaveAs Filename: fname

5) You could probably automate the emailing, but I've not done that in a
bit so am not sure where to start.

"KDP" wrote:

I have one workbook that I create every month and distribute to employees.
Every employee has their own version of this workbook.

Normally, I create the blank workbook and do a File-SaveAs (their operator
#/name).

Instead of doing this 30 times and saving as each persons name/number, is it
possible to key a list of their names and run a macro (or something else) to
automatically create a file with each persons name???

Create a new workbook with a single sheet named Sheet1 with the names in A2:A31
(or as many as you need).

Then put this code in that new workbook's project.

Option Explicit
Sub MultiSave()

Dim myRng As Range
Dim myCell As Range
Dim myFolderName As String
Dim ErrorCtr As Long
Dim ListWks As Worksheet

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the workbook to be multi-saved"
Exit Sub
End If

myFolderName = "C:\temp\" '<-- change this
If Right(myFolderName, 1) < "\" Then
myFolderName = myFolderName & "\"
End If

Set ListWks = ThisWorkbook.Worksheets("Sheet1")
With ListWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

ErrorCtr = 0
For Each myCell In myRng.Cells
On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value
If Err.Number < 0 Then
myCell.Offset(0, 1).Value = "Error"
ErrorCtr = ErrorCtr + 1
Err.Clear
Else
myCell.Offset(0, 1).Value = "Ok"
End If
On Error GoTo 0
Next myCell

If ErrorCtr = 0 Then
MsgBox "Done with no errors!"
Else
MsgBox "Errors found. Check this sheet"
Application.Goto ListWks.Range("a1")
End If

End Sub

Change the folder name to save to.

(each time you run this, any existing file will be overwritten!)

And then save this workbook as a nice name:
WorkbookThatSavesMultipleTimes.xls

Then open your "real" workbook that gets saved. Make that workbook the
activeworkbook.

Alt-F8
choose the macro named MultiSave
and test it out.

Look at column B of the List worksheet. You'll see error's or ok's for each
name. These get updated each time you run the macro.


KDP wrote:

Barb - I've never done this before, but this is what I've got. When I run the
macro, it gives me the message:

Compile Error:

Expected Sub, Function, or Property

and the fname at the end is highlighted...

------------------------------------------------------

Sub Open1()
'
' Open1 Macro
' Macro recorded 3/29/2007 by b39769
'

'
Workbooks.Open Filename:="H:\oWb.xls"

For i = 2 To aws.Cells(Rows.Count, 1).End(xlUp).Row
fname = aws.Cells(i, 1).Value
Next i

oWB.SaveCopyAs Filename: fname

End Sub

-----------------------------------------------------

i have hardly any idea what i'm doing...i've gotta get a book or something
on this.

THANKS!

"Barb Reinhardt" wrote:

I can envision how I'd do this, but it would take some time to create the
code. This is what I'd do:

1) Create a workbook with the list of users ids that you want to use. I'd
probably call this aWB. Refer to the sheet with the user ids as aWS.
2) Create a macro to open the file that you want to perform the multiple
save as on. Use something like this
http://www.mrexcel.com/td0009.html
I'd probably refer to this workbook as oWB
3) Extract the user names from that worksheet. I'd put the first name in
row 2 column 1. I'd use something like

for i = 2 to aws.cells(rows.count,1).end(xlup)
fname = aws.cells(i,1).value
next i

4) WIthin the for/next loop, do the following

oWB.SaveAs Filename: fname

5) You could probably automate the emailing, but I've not done that in a
bit so am not sure where to start.

"KDP" wrote:

I have one workbook that I create every month and distribute to employees.
Every employee has their own version of this workbook.

Normally, I create the blank workbook and do a File-SaveAs (their operator
#/name).

Instead of doing this 30 times and saving as each persons name/number, is it
possible to key a list of their names and run a macro (or something else) to
automatically create a file with each persons name???


--

Dave Peterson

THANKS DAVE!!! i'll try it as soon as i get to work!

"Dave Peterson" wrote:

Create a new workbook with a single sheet named Sheet1 with the names in A2:A31
(or as many as you need).

Then put this code in that new workbook's project.

Option Explicit
Sub MultiSave()

Dim myRng As Range
Dim myCell As Range
Dim myFolderName As String
Dim ErrorCtr As Long
Dim ListWks As Worksheet

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the workbook to be multi-saved"
Exit Sub
End If

myFolderName = "C:\temp\" '<-- change this
If Right(myFolderName, 1) < "\" Then
myFolderName = myFolderName & "\"
End If

Set ListWks = ThisWorkbook.Worksheets("Sheet1")
With ListWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

ErrorCtr = 0
For Each myCell In myRng.Cells
On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value
If Err.Number < 0 Then
myCell.Offset(0, 1).Value = "Error"
ErrorCtr = ErrorCtr + 1
Err.Clear
Else
myCell.Offset(0, 1).Value = "Ok"
End If
On Error GoTo 0
Next myCell

If ErrorCtr = 0 Then
MsgBox "Done with no errors!"
Else
MsgBox "Errors found. Check this sheet"
Application.Goto ListWks.Range("a1")
End If

End Sub

Change the folder name to save to.

(each time you run this, any existing file will be overwritten!)

And then save this workbook as a nice name:
WorkbookThatSavesMultipleTimes.xls

Then open your "real" workbook that gets saved. Make that workbook the
activeworkbook.

Alt-F8
choose the macro named MultiSave
and test it out.

Look at column B of the List worksheet. You'll see error's or ok's for each
name. These get updated each time you run the macro.


KDP wrote:

Barb - I've never done this before, but this is what I've got. When I run the
macro, it gives me the message:

Compile Error:

Expected Sub, Function, or Property

and the fname at the end is highlighted...

------------------------------------------------------

Sub Open1()
'
' Open1 Macro
' Macro recorded 3/29/2007 by b39769
'

'
Workbooks.Open Filename:="H:\oWb.xls"

For i = 2 To aws.Cells(Rows.Count, 1).End(xlUp).Row
fname = aws.Cells(i, 1).Value
Next i

oWB.SaveCopyAs Filename: fname

End Sub

-----------------------------------------------------

i have hardly any idea what i'm doing...i've gotta get a book or something
on this.

THANKS!

"Barb Reinhardt" wrote:

I can envision how I'd do this, but it would take some time to create the
code. This is what I'd do:

1) Create a workbook with the list of users ids that you want to use. I'd
probably call this aWB. Refer to the sheet with the user ids as aWS.
2) Create a macro to open the file that you want to perform the multiple
save as on. Use something like this
http://www.mrexcel.com/td0009.html
I'd probably refer to this workbook as oWB
3) Extract the user names from that worksheet. I'd put the first name in
row 2 column 1. I'd use something like

for i = 2 to aws.cells(rows.count,1).end(xlup)
fname = aws.cells(i,1).value
next i

4) WIthin the for/next loop, do the following

oWB.SaveAs Filename: fname

5) You could probably automate the emailing, but I've not done that in a
bit so am not sure where to start.

"KDP" wrote:

I have one workbook that I create every month and distribute to employees.
Every employee has their own version of this workbook.

Normally, I create the blank workbook and do a File-SaveAs (their operator
#/name).

Instead of doing this 30 times and saving as each persons name/number, is it
possible to key a list of their names and run a macro (or something else) to
automatically create a file with each persons name???


--

Dave Peterson

Dave, this is what i have...
--------------------------------------------------------
Option Explicit
Sub MultiSave()

Dim myRng As Range
Dim myCell As Range
Dim myFolderName As String
Dim ErrorCtr As Long
Dim ListWks As Worksheet

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the workbook to be multi-saved"
Exit Sub
End If

myFolderName = "C:\Documents and Settings\b39769\Desktop"
If Right(myFolderName, 1) < "\" Then
myFolderName = myFolderName & "\"
End If

Set ListWks = ThisWorkbook.Worksheets("Sheet1")
With ListWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

ErrorCtr = 0
For Each myCell In myRng.Cells
On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value
If Err.Number < 0 Then
myCell.Offset(0, 1).Value = "Error"
ErrorCtr = ErrorCtr + 1
Err.Clear
Else
myCell.Offset(0, 1).Value = "Ok"
End If
On Error GoTo 0
Next myCell

If ErrorCtr = 0 Then
MsgBox "Done with no errors!"
Else
MsgBox "Errors found. Check this sheet"
Application.Goto ListWks.Range("a1")
End If

End Sub
-------------------------------------------------

And i'm getting the "Errors found, check this sheet" message...

what am i doing wrong? or havent done??




"Dave Peterson" wrote:

Create a new workbook with a single sheet named Sheet1 with the names in A2:A31
(or as many as you need).

Then put this code in that new workbook's project.

Option Explicit
Sub MultiSave()

Dim myRng As Range
Dim myCell As Range
Dim myFolderName As String
Dim ErrorCtr As Long
Dim ListWks As Worksheet

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the workbook to be multi-saved"
Exit Sub
End If

myFolderName = "C:\temp\" '<-- change this
If Right(myFolderName, 1) < "\" Then
myFolderName = myFolderName & "\"
End If

Set ListWks = ThisWorkbook.Worksheets("Sheet1")
With ListWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

ErrorCtr = 0
For Each myCell In myRng.Cells
On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value
If Err.Number < 0 Then
myCell.Offset(0, 1).Value = "Error"
ErrorCtr = ErrorCtr + 1
Err.Clear
Else
myCell.Offset(0, 1).Value = "Ok"
End If
On Error GoTo 0
Next myCell

If ErrorCtr = 0 Then
MsgBox "Done with no errors!"
Else
MsgBox "Errors found. Check this sheet"
Application.Goto ListWks.Range("a1")
End If

End Sub

Change the folder name to save to.

(each time you run this, any existing file will be overwritten!)

And then save this workbook as a nice name:
WorkbookThatSavesMultipleTimes.xls

Then open your "real" workbook that gets saved. Make that workbook the
activeworkbook.

Alt-F8
choose the macro named MultiSave
and test it out.

Look at column B of the List worksheet. You'll see error's or ok's for each
name. These get updated each time you run the macro.


KDP wrote:

Barb - I've never done this before, but this is what I've got. When I run the
macro, it gives me the message:

Compile Error:

Expected Sub, Function, or Property

and the fname at the end is highlighted...

------------------------------------------------------

Sub Open1()
'
' Open1 Macro
' Macro recorded 3/29/2007 by b39769
'

'
Workbooks.Open Filename:="H:\oWb.xls"

For i = 2 To aws.Cells(Rows.Count, 1).End(xlUp).Row
fname = aws.Cells(i, 1).Value
Next i

oWB.SaveCopyAs Filename: fname

End Sub

-----------------------------------------------------

i have hardly any idea what i'm doing...i've gotta get a book or something
on this.

THANKS!

"Barb Reinhardt" wrote:

I can envision how I'd do this, but it would take some time to create the
code. This is what I'd do:

1) Create a workbook with the list of users ids that you want to use. I'd
probably call this aWB. Refer to the sheet with the user ids as aWS.
2) Create a macro to open the file that you want to perform the multiple
save as on. Use something like this
http://www.mrexcel.com/td0009.html
I'd probably refer to this workbook as oWB
3) Extract the user names from that worksheet. I'd put the first name in
row 2 column 1. I'd use something like

for i = 2 to aws.cells(rows.count,1).end(xlup)
fname = aws.cells(i,1).value
next i

4) WIthin the for/next loop, do the following

oWB.SaveAs Filename: fname

5) You could probably automate the emailing, but I've not done that in a
bit so am not sure where to start.

"KDP" wrote:

I have one workbook that I create every month and distribute to employees.
Every employee has their own version of this workbook.

Normally, I create the blank workbook and do a File-SaveAs (their operator
#/name).

Instead of doing this 30 times and saving as each persons name/number, is it
possible to key a list of their names and run a macro (or something else) to
automatically create a file with each persons name???


--

Dave Peterson

When you look in column B of that worksheet, you'll see cells that have "Error"
in them. What's in column A of that row?

If all of them show up as error, then maybe the problem is in the name of the
folder.

Are you sure that you have a folder named:
"C:\Documents and Settings\b39769\Desktop"



KDP wrote:

Dave, this is what i have...
--------------------------------------------------------
Option Explicit
Sub MultiSave()

Dim myRng As Range
Dim myCell As Range
Dim myFolderName As String
Dim ErrorCtr As Long
Dim ListWks As Worksheet

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the workbook to be multi-saved"
Exit Sub
End If

myFolderName = "C:\Documents and Settings\b39769\Desktop"
If Right(myFolderName, 1) < "\" Then
myFolderName = myFolderName & "\"
End If

Set ListWks = ThisWorkbook.Worksheets("Sheet1")
With ListWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

ErrorCtr = 0
For Each myCell In myRng.Cells
On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value
If Err.Number < 0 Then
myCell.Offset(0, 1).Value = "Error"
ErrorCtr = ErrorCtr + 1
Err.Clear
Else
myCell.Offset(0, 1).Value = "Ok"
End If
On Error GoTo 0
Next myCell

If ErrorCtr = 0 Then
MsgBox "Done with no errors!"
Else
MsgBox "Errors found. Check this sheet"
Application.Goto ListWks.Range("a1")
End If

End Sub
-------------------------------------------------

And i'm getting the "Errors found, check this sheet" message...

what am i doing wrong? or havent done??

"Dave Peterson" wrote:

Create a new workbook with a single sheet named Sheet1 with the names in A2:A31
(or as many as you need).

Then put this code in that new workbook's project.

Option Explicit
Sub MultiSave()

Dim myRng As Range
Dim myCell As Range
Dim myFolderName As String
Dim ErrorCtr As Long
Dim ListWks As Worksheet

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the workbook to be multi-saved"
Exit Sub
End If

myFolderName = "C:\temp\" '<-- change this
If Right(myFolderName, 1) < "\" Then
myFolderName = myFolderName & "\"
End If

Set ListWks = ThisWorkbook.Worksheets("Sheet1")
With ListWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

ErrorCtr = 0
For Each myCell In myRng.Cells
On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value
If Err.Number < 0 Then
myCell.Offset(0, 1).Value = "Error"
ErrorCtr = ErrorCtr + 1
Err.Clear
Else
myCell.Offset(0, 1).Value = "Ok"
End If
On Error GoTo 0
Next myCell

If ErrorCtr = 0 Then
MsgBox "Done with no errors!"
Else
MsgBox "Errors found. Check this sheet"
Application.Goto ListWks.Range("a1")
End If

End Sub

Change the folder name to save to.

(each time you run this, any existing file will be overwritten!)

And then save this workbook as a nice name:
WorkbookThatSavesMultipleTimes.xls

Then open your "real" workbook that gets saved. Make that workbook the
activeworkbook.

Alt-F8
choose the macro named MultiSave
and test it out.

Look at column B of the List worksheet. You'll see error's or ok's for each
name. These get updated each time you run the macro.


KDP wrote:

Barb - I've never done this before, but this is what I've got. When I run the
macro, it gives me the message:

Compile Error:

Expected Sub, Function, or Property

and the fname at the end is highlighted...

------------------------------------------------------

Sub Open1()
'
' Open1 Macro
' Macro recorded 3/29/2007 by b39769
'

'
Workbooks.Open Filename:="H:\oWb.xls"

For i = 2 To aws.Cells(Rows.Count, 1).End(xlUp).Row
fname = aws.Cells(i, 1).Value
Next i

oWB.SaveCopyAs Filename: fname

End Sub

-----------------------------------------------------

i have hardly any idea what i'm doing...i've gotta get a book or something
on this.

THANKS!

"Barb Reinhardt" wrote:

I can envision how I'd do this, but it would take some time to create the
code. This is what I'd do:

1) Create a workbook with the list of users ids that you want to use. I'd
probably call this aWB. Refer to the sheet with the user ids as aWS.
2) Create a macro to open the file that you want to perform the multiple
save as on. Use something like this
http://www.mrexcel.com/td0009.html
I'd probably refer to this workbook as oWB
3) Extract the user names from that worksheet. I'd put the first name in
row 2 column 1. I'd use something like

for i = 2 to aws.cells(rows.count,1).end(xlup)
fname = aws.cells(i,1).value
next i

4) WIthin the for/next loop, do the following

oWB.SaveAs Filename: fname

5) You could probably automate the emailing, but I've not done that in a
bit so am not sure where to start.

"KDP" wrote:

I have one workbook that I create every month and distribute to employees.
Every employee has their own version of this workbook.

Normally, I create the blank workbook and do a File-SaveAs (their operator
#/name).

Instead of doing this 30 times and saving as each persons name/number, is it
possible to key a list of their names and run a macro (or something else) to
automatically create a file with each persons name???


--

Dave Peterson


--

Dave Peterson