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

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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


--

Dave Peterson

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"


---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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


--

Dave Peterson

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

DAVE IT WORKED!!!

it was just a matter of getting the macro to save under the correct
workbook, etc.

THANK YOU THANK YOU THANK YOU!!!
---------------------------------------------------
"Dave Peterson" wrote:

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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


--

Dave Peterson


--

Whew!

I was out of guesses.

KDP wrote:

DAVE IT WORKED!!!

it was just a matter of getting the macro to save under the correct
workbook, etc.

THANK YOU THANK YOU THANK YOU!!!
---------------------------------------------------
"Dave Peterson" wrote:

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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


--

Dave Peterson


--

--

Dave Peterson

Dave,

everything is working fine...

i just have an 'out of curiosity' question.

when the workbooks are saved to the file, Instead of having the Excel logo,
they have the Windows logo...and they open as excel files.

is there any reason behind this?

-Kevin

"Dave Peterson" wrote:

Whew!

I was out of guesses.

KDP wrote:

DAVE IT WORKED!!!

it was just a matter of getting the macro to save under the correct
workbook, etc.

THANK YOU THANK YOU THANK YOU!!!
---------------------------------------------------
"Dave Peterson" wrote:

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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???


--

Maybe a small change to the program will fix this:

ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value & ".xls"

(Sorry about that!)

I did test, but I didn't take the time to look at the filenames and I should
have.


KDP wrote:

Dave,

everything is working fine...

i just have an 'out of curiosity' question.

when the workbooks are saved to the file, Instead of having the Excel logo,
they have the Windows logo...and they open as excel files.

is there any reason behind this?

-Kevin

"Dave Peterson" wrote:

Whew!

I was out of guesses.

KDP wrote:

DAVE IT WORKED!!!

it was just a matter of getting the macro to save under the correct
workbook, etc.

THANK YOU THANK YOU THANK YOU!!!
---------------------------------------------------
"Dave Peterson" wrote:

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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,

you are the man.!

now that i know this is possible....

i have another question..is it possible to alter this somehow to make it
work in a similar way but differently...

Can i make it create the worksheets in ONE workbook (basically create the
default file that i would later duplicate for users, has about 25 sheets) and
pull from another list (this list will be dates)?

---------------------------------------------------
"Dave Peterson" wrote:

Maybe a small change to the program will fix this:

ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value & ".xls"

(Sorry about that!)

I did test, but I didn't take the time to look at the filenames and I should
have.


KDP wrote:

Dave,

everything is working fine...

i just have an 'out of curiosity' question.

when the workbooks are saved to the file, Instead of having the Excel logo,
they have the Windows logo...and they open as excel files.

is there any reason behind this?

-Kevin

"Dave Peterson" wrote:

Whew!

I was out of guesses.

KDP wrote:

DAVE IT WORKED!!!

it was just a matter of getting the macro to save under the correct
workbook, etc.

THANK YOU THANK YOU THANK YOU!!!
---------------------------------------------------
"Dave Peterson" wrote:

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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

I don't understand...

But I bet it's possible <bg.

KDP wrote:

DAVE,

you are the man.!

now that i know this is possible....

i have another question..is it possible to alter this somehow to make it
work in a similar way but differently...

Can i make it create the worksheets in ONE workbook (basically create the
default file that i would later duplicate for users, has about 25 sheets) and
pull from another list (this list will be dates)?

---------------------------------------------------
"Dave Peterson" wrote:

Maybe a small change to the program will fix this:

ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value & ".xls"

(Sorry about that!)

I did test, but I didn't take the time to look at the filenames and I should
have.


KDP wrote:

Dave,

everything is working fine...

i just have an 'out of curiosity' question.

when the workbooks are saved to the file, Instead of having the Excel logo,
they have the Windows logo...and they open as excel files.

is there any reason behind this?

-Kevin

"Dave Peterson" wrote:

Whew!

I was out of guesses.

KDP wrote:

DAVE IT WORKED!!!

it was just a matter of getting the macro to save under the correct
workbook, etc.

THANK YOU THANK YOU THANK YOU!!!
---------------------------------------------------
"Dave Peterson" wrote:

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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

--

Dave Peterson

LOL. thanks for ur helP!

"Dave Peterson" wrote:

I don't understand...

But I bet it's possible <bg.

KDP wrote:

DAVE,

you are the man.!

now that i know this is possible....

i have another question..is it possible to alter this somehow to make it
work in a similar way but differently...

Can i make it create the worksheets in ONE workbook (basically create the
default file that i would later duplicate for users, has about 25 sheets) and
pull from another list (this list will be dates)?

---------------------------------------------------
"Dave Peterson" wrote:

Maybe a small change to the program will fix this:

ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value & ".xls"

(Sorry about that!)

I did test, but I didn't take the time to look at the filenames and I should
have.


KDP wrote:

Dave,

everything is working fine...

i just have an 'out of curiosity' question.

when the workbooks are saved to the file, Instead of having the Excel logo,
they have the Windows logo...and they open as excel files.

is there any reason behind this?

-Kevin

"Dave Peterson" wrote:

Whew!

I was out of guesses.

KDP wrote:

DAVE IT WORKED!!!

it was just a matter of getting the macro to save under the correct
workbook, etc.

THANK YOU THANK YOU THANK YOU!!!
---------------------------------------------------
"Dave Peterson" wrote:

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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

This thread is getting kind of long.

You may want to post the details of what you want in a new thread. It kind of
sounded like it would be a different macro (to me anyway).

KDP wrote:

LOL. thanks for ur helP!

"Dave Peterson" wrote:

I don't understand...

But I bet it's possible <bg.

KDP wrote:

DAVE,

you are the man.!

now that i know this is possible....

i have another question..is it possible to alter this somehow to make it
work in a similar way but differently...

Can i make it create the worksheets in ONE workbook (basically create the
default file that i would later duplicate for users, has about 25 sheets) and
pull from another list (this list will be dates)?

---------------------------------------------------
"Dave Peterson" wrote:

Maybe a small change to the program will fix this:

ActiveWorkbook.SaveCopyAs Filename:=myFolderName & myCell.Value & ".xls"

(Sorry about that!)

I did test, but I didn't take the time to look at the filenames and I should
have.


KDP wrote:

Dave,

everything is working fine...

i just have an 'out of curiosity' question.

when the workbooks are saved to the file, Instead of having the Excel logo,
they have the Windows logo...and they open as excel files.

is there any reason behind this?

-Kevin

"Dave Peterson" wrote:

Whew!

I was out of guesses.

KDP wrote:

DAVE IT WORKED!!!

it was just a matter of getting the macro to save under the correct
workbook, etc.

THANK YOU THANK YOU THANK YOU!!!
---------------------------------------------------
"Dave Peterson" wrote:

The macro should be in the same workbook as the worksheet that has the list.

If there are errors, then this line:
Application.Goto ListWks.Range("a1")

will show you the worksheet that you specified that contained the list. Do you
see the list when you run this and have errors?



KDP wrote:

yeah,

that is where the list is located...

does it matter when i pull up available macros (Alt+F8) , that the macro
isnt stored in "This workbook", it's listed under "All open workbooks" or
"personal.xls"

---------------------------------------------------
"Dave Peterson" wrote:

The code picks up the names from the workbook and worksheet that is specified in
this line:

Set ListWks = ThisWorkbook.Worksheets("Sheet1")

Thisworkbook is the workbook that owns the code
(workbookthatsavesmutlipletimes.xls).

The sheet is named Sheet1.

Is that where your list is located?

If not, you can rename sheets or fix that line to point at the correct worksheet
name.

KDP wrote:

This is what i have in column A of the workbook. 02 starts in cell A2. there
is nothing in column B. I tried saving them to my desktop, but it didnt work,
so i went in and created a folder "C:\TEMP\Test". I saved a file to this
folder and went into the properties to double check the location for this.

02 - N. Halas
04 - L. Yakimenko
05 - A. Minasyan
06 - T. Queen
07 - S. Darby
15 - L. Tkacheva
17 - A. Conde
18 - T. Ross
20 - G. Breus
21 - C. Moise
25 - M. Rybak
27 - J. Friday
29 - C. Moore
30 - P. Nguyen
31 - Y. Amengonu
32 - N. Kushnir
33 - D. Clark
34 - C. Blanding

(these are the file names that i need created and they are saved in a
workbook (workbookthatsavesmutlipletimes.xls))

---------------------------------------------------
"Dave Peterson" wrote:

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

--

Dave Peterson