Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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??? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 -- |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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??? -- |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save as from a list
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a list of all the files in My Documents to save in Excel. | Excel Discussion (Misc queries) | |||
save a entry in database for the items in list | New Users to Excel | |||
How to..use worksheet form and save data to an Excel list | Excel Discussion (Misc queries) | |||
drop down list - save the entry | Excel Discussion (Misc queries) | |||
How to save an Exel-sheet with over 10,000 list-/validation-field. | Excel Discussion (Misc queries) |