Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
I currently have a worksheet named 1-1-2007. I need to copy this sheet 60
times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Mike
Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Thank you. Works perfect. Mike "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Thanks for the feedback.
Thanks to Dave also for the code. Gord On Wed, 28 Feb 2007 11:42:07 -0800, MikeD1224 wrote: Thank you. Works perfect. Mike "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Option explicit
sub testme() dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub Click on the first sheet tab to be saved and ctrl-click on subsequent. Then run the macro. MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Sorry, one last question... I have a date in cell A2 on each worksheet. The names of my worksheets are all dates also. Is it possible, that as each sheet is created and renamed, that I can put the sheet name in cell A2 for that sheet? Let me know if you have any ideas. Thank you so much. Mike "Gord Dibben" wrote: Thanks for the feedback. Thanks to Dave also for the code. Gord On Wed, 28 Feb 2007 11:42:07 -0800, MikeD1224 wrote: Thank you. Works perfect. Mike "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Can this be tweaked to not only save each worksheet as its own workbook, but
to also save the worksheet called "Schedule" into each workbook as the first sheet? "Dave Peterson" wrote: Option explicit sub testme() dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub Click on the first sheet tab to be saved and ctrl-click on subsequent. Then run the macro. MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Mike
Make changes as such.......... On Error Resume Next With ActiveSheet .Name = myCell.Value .Range("A2").Value = myCell.Value End With If Err.Number < 0 Then Gord Wed, 28 Feb 2007 12:05:36 -0800, MikeD1224 wrote: Sorry, one last question... I have a date in cell A2 on each worksheet. The names of my worksheets are all dates also. Is it possible, that as each sheet is created and renamed, that I can put the sheet name in cell A2 for that sheet? Let me know if you have any ideas. Thank you so much. Mike "Gord Dibben" wrote: Thanks for the feedback. Thanks to Dave also for the code. Gord On Wed, 28 Feb 2007 11:42:07 -0800, MikeD1224 wrote: Thank you. Works perfect. Mike "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
See Dave's post for making new books from sheets.
Gord On Wed, 28 Feb 2007 11:48:13 -0800, MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Option Explicit
Sub testme() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets Worksheets(Array("schedule", wks.Name)).Copy With ActiveSheet .Parent.Worksheets("schedule").Move _ befo=.Parent.Worksheets(1) With .Parent.Worksheets(wks.Name).Range("A2") .NumberFormat = "mm/dd/yyyy" .Value = wks.Name End With .Parent.SaveAs Filename:="C:\temp\" & wks.Name & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With Next wks End Sub MikeD1224 wrote: Can this be tweaked to not only save each worksheet as its own workbook, but to also save the worksheet called "Schedule" into each workbook as the first sheet? "Dave Peterson" wrote: Option explicit sub testme() dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub Click on the first sheet tab to be saved and ctrl-click on subsequent. Then run the macro. MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
I'm using the following macro to take the value in cell B2 and save each
worksheet and the worksheet called "Schedule" into their own workbooks. It keeps hanging at the line that reads ".value = wks.name Any ideas how to fix this? Mike Option Explicit Sub testme() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets Worksheets(Array("Schedule", wks.Name)).Copy With ActiveSheet ..Parent.Worksheets("Schedule").Move _ befo=.Parent.Worksheets(1) With .Parent.Worksheets(wks.Name).Range("B2") ..NumberFormat = "text" ..Value = wks.Name End With ..Parent.SaveAs Filename:="\\bdfiler\masterads\2007 Master Ad" & wks.Name & ".xls", _ FileFormat:=xlWorkbookNormal ..Parent.Close savechanges:=False End With Next wks End Sub "Gord Dibben" wrote: See Dave's post for making new books from sheets. Gord On Wed, 28 Feb 2007 11:48:13 -0800, MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
First, change this line:
..NumberFormat = "text" to ..NumberFormat = "@" @ means that the cell should be formatted as text. Is that worksheet that you're copying protected (with B2 locked)? MikeD1224 wrote: I'm using the following macro to take the value in cell B2 and save each worksheet and the worksheet called "Schedule" into their own workbooks. It keeps hanging at the line that reads ".value = wks.name Any ideas how to fix this? Mike Option Explicit Sub testme() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets Worksheets(Array("Schedule", wks.Name)).Copy With ActiveSheet .Parent.Worksheets("Schedule").Move _ befo=.Parent.Worksheets(1) With .Parent.Worksheets(wks.Name).Range("B2") .NumberFormat = "text" .Value = wks.Name End With .Parent.SaveAs Filename:="\\bdfiler\masterads\2007 Master Ad" & wks.Name & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With Next wks End Sub "Gord Dibben" wrote: See Dave's post for making new books from sheets. Gord On Wed, 28 Feb 2007 11:48:13 -0800, MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
And if that doesn't help, what is the error message that you get?
Dave Peterson wrote: First, change this line: .NumberFormat = "text" to .NumberFormat = "@" @ means that the cell should be formatted as text. Is that worksheet that you're copying protected (with B2 locked)? MikeD1224 wrote: I'm using the following macro to take the value in cell B2 and save each worksheet and the worksheet called "Schedule" into their own workbooks. It keeps hanging at the line that reads ".value = wks.name Any ideas how to fix this? Mike Option Explicit Sub testme() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets Worksheets(Array("Schedule", wks.Name)).Copy With ActiveSheet .Parent.Worksheets("Schedule").Move _ befo=.Parent.Worksheets(1) With .Parent.Worksheets(wks.Name).Range("B2") .NumberFormat = "text" .Value = wks.Name End With .Parent.SaveAs Filename:="\\bdfiler\masterads\2007 Master Ad" & wks.Name & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With Next wks End Sub "Gord Dibben" wrote: See Dave's post for making new books from sheets. Gord On Wed, 28 Feb 2007 11:48:13 -0800, MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
The error message is
"Run-Time Error 1004: unable to set the NumberFormat Property of the Range Class. The cell is formatted in the following format: 14-Mar-07. "Dave Peterson" wrote: And if that doesn't help, what is the error message that you get? Dave Peterson wrote: First, change this line: .NumberFormat = "text" to .NumberFormat = "@" @ means that the cell should be formatted as text. Is that worksheet that you're copying protected (with B2 locked)? MikeD1224 wrote: I'm using the following macro to take the value in cell B2 and save each worksheet and the worksheet called "Schedule" into their own workbooks. It keeps hanging at the line that reads ".value = wks.name Any ideas how to fix this? Mike Option Explicit Sub testme() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets Worksheets(Array("Schedule", wks.Name)).Copy With ActiveSheet .Parent.Worksheets("Schedule").Move _ befo=.Parent.Worksheets(1) With .Parent.Worksheets(wks.Name).Range("B2") .NumberFormat = "text" .Value = wks.Name End With .Parent.SaveAs Filename:="\\bdfiler\masterads\2007 Master Ad" & wks.Name & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With Next wks End Sub "Gord Dibben" wrote: See Dave's post for making new books from sheets. Gord On Wed, 28 Feb 2007 11:48:13 -0800, MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create and name multiple sheets
Is that worksheet that you're copying protected (with B2 locked)?
MikeD1224 wrote: The error message is "Run-Time Error 1004: unable to set the NumberFormat Property of the Range Class. The cell is formatted in the following format: 14-Mar-07. "Dave Peterson" wrote: And if that doesn't help, what is the error message that you get? Dave Peterson wrote: First, change this line: .NumberFormat = "text" to .NumberFormat = "@" @ means that the cell should be formatted as text. Is that worksheet that you're copying protected (with B2 locked)? MikeD1224 wrote: I'm using the following macro to take the value in cell B2 and save each worksheet and the worksheet called "Schedule" into their own workbooks. It keeps hanging at the line that reads ".value = wks.name Any ideas how to fix this? Mike Option Explicit Sub testme() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets Worksheets(Array("Schedule", wks.Name)).Copy With ActiveSheet .Parent.Worksheets("Schedule").Move _ befo=.Parent.Worksheets(1) With .Parent.Worksheets(wks.Name).Range("B2") .NumberFormat = "text" .Value = wks.Name End With .Parent.SaveAs Filename:="\\bdfiler\masterads\2007 Master Ad" & wks.Name & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With Next wks End Sub "Gord Dibben" wrote: See Dave's post for making new books from sheets. Gord On Wed, 28 Feb 2007 11:48:13 -0800, MikeD1224 wrote: Gord, Do you also know how I can save each worksheet as a workbook with the name of the file being derived from the worksheet name? "Gord Dibben" wrote: Mike Sheet1 will contain your list of names. Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: 1-1-2007 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("1-1-2007") Set ListWks = Worksheets("list") With ListWks Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = myCell.Value If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224 wrote: I currently have a worksheet named 1-1-2007. I need to copy this sheet 60 times and rename each. The names for each sheet are contained on Sheet1 (named "Schedule") and are in column C. Can I use a macro or an array formula to do this? Any help would be great. Thanks, Mike -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CREATE MULTIPLE WORK SHEETS IN A WORKBOOK | Excel Worksheet Functions | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to create workbook with multiple sheets | Excel Discussion (Misc queries) | |||
Pivot Table--How can I create from multiple sheets? | New Users to Excel | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions |