Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automate adding sheets
I would like to automate adding sheets to my workbook. What I need to do is
add 100 sheets at a time using a template so that I can fill in my fields with information .. But as the sheets are added they need to be auto named in sequence starting with the last one in the work book and continue from that number ..Example my work book starts at c80000 and goes from there . All my tabs for the sheets are named c8000, c8001 , c8002 and so on. The other thing I am trying to do is add the sheet number to field A 2 on each sheet . Is this possible ?? I am new to excel and have been reading books and trying different things but no luck !! THANKS !! -- Brian |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automate adding sheets
On a sheet named "list" create the list of sheet names.
In A1 enter c8000 In A2 enter c8001 Drag/copy down to A101 Your sheet to copy is named "Template" Run this macro. 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: Template ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("Template") 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 With ActiveSheet .Name = myCell.Value .Range("A1") = .Name End With 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 Wed, 18 Jun 2008 15:45:01 -0700, Brian wrote: I would like to automate adding sheets to my workbook. What I need to do is add 100 sheets at a time using a template so that I can fill in my fields with information . But as the sheets are added they need to be auto named in sequence starting with the last one in the work book and continue from that number .Example my work book starts at c80000 and goes from there . All my tabs for the sheets are named c8000, c8001 , c8002 and so on. The other thing I am trying to do is add the sheet number to field A 2 on each sheet . Is this possible ?? I am new to excel and have been reading books and trying different things but no luck !! THANKS !! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automate adding sheets
That works perfect except for 1 thing , if I run the macro again in the same
work book it comes up with a error block . It states fix error in template 2 and it creates sheets named template 2 , template 3 and so on . The macro does not pick up from the last tab entry. I figured out that you macro refrences back to the list sheet. But how would I get it to refrence to the last sheet that was entered by the macro and add 100 more sheets from that tab name (numbered sequence)and continue assending . The other part is how to make it reference back and use the template?? -- Brian "Gord Dibben" wrote: On a sheet named "list" create the list of sheet names. In A1 enter c8000 In A2 enter c8001 Drag/copy down to A101 Your sheet to copy is named "Template" Run this macro. 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: Template ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Set TemplateWks = Worksheets("Template") 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 With ActiveSheet .Name = myCell.Value .Range("A1") = .Name End With 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 Wed, 18 Jun 2008 15:45:01 -0700, Brian wrote: I would like to automate adding sheets to my workbook. What I need to do is add 100 sheets at a time using a template so that I can fill in my fields with information . But as the sheets are added they need to be auto named in sequence starting with the last one in the work book and continue from that number .Example my work book starts at c80000 and goes from there . All my tabs for the sheets are named c8000, c8001 , c8002 and so on. The other thing I am trying to do is add the sheet number to field A 2 on each sheet . Is this possible ?? I am new to excel and have been reading books and trying different things but no luck !! THANKS !! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automate adding sheets
Dave.........HELP!!!
I'll have to work on this for a while but easiest method would be to just change the list in the list sheet before running the second time. i.e. delete c8000 to c8099 and replace with c8100 to c8199 For now........amended code with inputbox to enter a number for list sheet A1 Sub CreateNameSheets() ' by Dave Peterson & Gord Dibben ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: Template ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Start As String Dim Lrow As Long Set TemplateWks = Worksheets("Template") Set ListWks = Worksheets("list") Lrow = 100 Start = InputBox("enter start number" & vbLf & _ "first run enter 000" & vbLf & _ "subsequent runs, enter last sheet number + 1") With ListWks .Columns(1).ClearContents .Range("A1").Value = "c8" & Start .Range("A1:A" & Lrow).DataSeries Rowcol:=xlColumns, Type:=xlAutoFill 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 With ActiveSheet .Name = myCell.Value .Range("A1") = .Name End With If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub I'm going to work on returning current lastsheet name to A1 if the macro has been run once. Gord On Wed, 18 Jun 2008 17:18:01 -0700, Brian wrote: That works perfect except for 1 thing , if I run the macro again in the same work book it comes up with a error block . It states fix error in template 2 and it creates sheets named template 2 , template 3 and so on . The macro does not pick up from the last tab entry. I figured out that you macro refrences back to the list sheet. But how would I get it to refrence to the last sheet that was entered by the macro and add 100 more sheets from that tab name (numbered sequence)and continue assending . The other part is how to make it reference back and use the template?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automate adding sheets
I think I'd drop the list worksheet and just ask for the starting number and how
many. Option Explicit Sub CreateNameSheets() Dim TemplateWks As Worksheet Dim Start As Long Dim HowMany As Long Dim iCtr As Long Dim NewWks As Worksheet Dim myName As String Set TemplateWks = Worksheets("Template") Start = Application.InputBox(Prompt:="Start with #", Type:=1) If Start = 0 Then Exit Sub HowMany = Application.InputBox(Prompt:="How Many More", Type:=1) If HowMany = 0 Then Exit Sub If HowMany 100 Then MsgBox "You're nuts!" Exit Sub End If For iCtr = Start To Start + HowMany myName = "C8" & Format(iCtr, "000") If SheetExists(myName, ActiveWorkbook) Then MsgBox "Sheet: " & myName & " already exists!" Else TemplateWks.Copy After:=Worksheets(Worksheets.Count) Set NewWks = ActiveSheet With NewWks 'no need for error checking. 'myName is ok and no worksheet with that name exists .Name = myName .Range("A2") = myName End With End If Next iCtr End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) On Error GoTo 0 End Function Gord Dibben wrote: Dave.........HELP!!! I'll have to work on this for a while but easiest method would be to just change the list in the list sheet before running the second time. i.e. delete c8000 to c8099 and replace with c8100 to c8199 For now........amended code with inputbox to enter a number for list sheet A1 Sub CreateNameSheets() ' by Dave Peterson & Gord Dibben ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: Template ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Start As String Dim Lrow As Long Set TemplateWks = Worksheets("Template") Set ListWks = Worksheets("list") Lrow = 100 Start = InputBox("enter start number" & vbLf & _ "first run enter 000" & vbLf & _ "subsequent runs, enter last sheet number + 1") With ListWks .Columns(1).ClearContents .Range("A1").Value = "c8" & Start .Range("A1:A" & Lrow).DataSeries Rowcol:=xlColumns, Type:=xlAutoFill 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 With ActiveSheet .Name = myCell.Value .Range("A1") = .Name End With If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub I'm going to work on returning current lastsheet name to A1 if the macro has been run once. Gord On Wed, 18 Jun 2008 17:18:01 -0700, Brian wrote: That works perfect except for 1 thing , if I run the macro again in the same work book it comes up with a error block . It states fix error in template 2 and it creates sheets named template 2 , template 3 and so on . The macro does not pick up from the last tab entry. I figured out that you macro refrences back to the list sheet. But how would I get it to refrence to the last sheet that was entered by the macro and add 100 more sheets from that tab name (numbered sequence)and continue assending . The other part is how to make it reference back and use the template?? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
automate adding sheets
Sounds good to me.
I was working on getting the lastsheet name/number automatically Thanks for the HELP!! Gord On Thu, 19 Jun 2008 12:37:14 -0500, Dave Peterson wrote: I think I'd drop the list worksheet and just ask for the starting number and how many. Option Explicit Sub CreateNameSheets() Dim TemplateWks As Worksheet Dim Start As Long Dim HowMany As Long Dim iCtr As Long Dim NewWks As Worksheet Dim myName As String Set TemplateWks = Worksheets("Template") Start = Application.InputBox(Prompt:="Start with #", Type:=1) If Start = 0 Then Exit Sub HowMany = Application.InputBox(Prompt:="How Many More", Type:=1) If HowMany = 0 Then Exit Sub If HowMany 100 Then MsgBox "You're nuts!" Exit Sub End If For iCtr = Start To Start + HowMany myName = "C8" & Format(iCtr, "000") If SheetExists(myName, ActiveWorkbook) Then MsgBox "Sheet: " & myName & " already exists!" Else TemplateWks.Copy After:=Worksheets(Worksheets.Count) Set NewWks = ActiveSheet With NewWks 'no need for error checking. 'myName is ok and no worksheet with that name exists .Name = myName .Range("A2") = myName End With End If Next iCtr End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) On Error GoTo 0 End Function Gord Dibben wrote: Dave.........HELP!!! I'll have to work on this for a while but easiest method would be to just change the list in the list sheet before running the second time. i.e. delete c8000 to c8099 and replace with c8100 to c8199 For now........amended code with inputbox to enter a number for list sheet A1 Sub CreateNameSheets() ' by Dave Peterson & Gord Dibben ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: Template ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Start As String Dim Lrow As Long Set TemplateWks = Worksheets("Template") Set ListWks = Worksheets("list") Lrow = 100 Start = InputBox("enter start number" & vbLf & _ "first run enter 000" & vbLf & _ "subsequent runs, enter last sheet number + 1") With ListWks .Columns(1).ClearContents .Range("A1").Value = "c8" & Start .Range("A1:A" & Lrow).DataSeries Rowcol:=xlColumns, Type:=xlAutoFill 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 With ActiveSheet .Name = myCell.Value .Range("A1") = .Name End With If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub I'm going to work on returning current lastsheet name to A1 if the macro has been run once. Gord On Wed, 18 Jun 2008 17:18:01 -0700, Brian wrote: That works perfect except for 1 thing , if I run the macro again in the same work book it comes up with a error block . It states fix error in template 2 and it creates sheets named template 2 , template 3 and so on . The macro does not pick up from the last tab entry. I figured out that you macro refrences back to the list sheet. But how would I get it to refrence to the last sheet that was entered by the macro and add 100 more sheets from that tab name (numbered sequence)and continue assending . The other part is how to make it reference back and use the template?? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
automate adding sheets
You guys are the best . I tried all kinds of things and nothing worked.
I put in your macro and wow it works great . I have a lot of applications this will be usefull for. -- Brian "Gord Dibben" wrote: Sounds good to me. I was working on getting the lastsheet name/number automatically Thanks for the HELP!! Gord On Thu, 19 Jun 2008 12:37:14 -0500, Dave Peterson wrote: I think I'd drop the list worksheet and just ask for the starting number and how many. Option Explicit Sub CreateNameSheets() Dim TemplateWks As Worksheet Dim Start As Long Dim HowMany As Long Dim iCtr As Long Dim NewWks As Worksheet Dim myName As String Set TemplateWks = Worksheets("Template") Start = Application.InputBox(Prompt:="Start with #", Type:=1) If Start = 0 Then Exit Sub HowMany = Application.InputBox(Prompt:="How Many More", Type:=1) If HowMany = 0 Then Exit Sub If HowMany 100 Then MsgBox "You're nuts!" Exit Sub End If For iCtr = Start To Start + HowMany myName = "C8" & Format(iCtr, "000") If SheetExists(myName, ActiveWorkbook) Then MsgBox "Sheet: " & myName & " already exists!" Else TemplateWks.Copy After:=Worksheets(Worksheets.Count) Set NewWks = ActiveSheet With NewWks 'no need for error checking. 'myName is ok and no worksheet with that name exists .Name = myName .Range("A2") = myName End With End If Next iCtr End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) On Error GoTo 0 End Function Gord Dibben wrote: Dave.........HELP!!! I'll have to work on this for a while but easiest method would be to just change the list in the list sheet before running the second time. i.e. delete c8000 to c8099 and replace with c8100 to c8199 For now........amended code with inputbox to enter a number for list sheet A1 Sub CreateNameSheets() ' by Dave Peterson & Gord Dibben ' List sheetnames required in col A in a sheet: List ' Sub will copy sheets based on the sheet named as: Template ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim Start As String Dim Lrow As Long Set TemplateWks = Worksheets("Template") Set ListWks = Worksheets("list") Lrow = 100 Start = InputBox("enter start number" & vbLf & _ "first run enter 000" & vbLf & _ "subsequent runs, enter last sheet number + 1") With ListWks .Columns(1).ClearContents .Range("A1").Value = "c8" & Start .Range("A1:A" & Lrow).DataSeries Rowcol:=xlColumns, Type:=xlAutoFill 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 With ActiveSheet .Name = myCell.Value .Range("A1") = .Name End With If Err.Number < 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub I'm going to work on returning current lastsheet name to A1 if the macro has been run once. Gord On Wed, 18 Jun 2008 17:18:01 -0700, Brian wrote: That works perfect except for 1 thing , if I run the macro again in the same work book it comes up with a error block . It states fix error in template 2 and it creates sheets named template 2 , template 3 and so on . The macro does not pick up from the last tab entry. I figured out that you macro refrences back to the list sheet. But how would I get it to refrence to the last sheet that was entered by the macro and add 100 more sheets from that tab name (numbered sequence)and continue assending . The other part is how to make it reference back and use the template?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel---how do i automate adding a suffix to text? | Excel Discussion (Misc queries) | |||
Automate adding in a reference number if the adjacent cell contains text... | Excel Worksheet Functions | |||
Adding Sheets | Excel Discussion (Misc queries) | |||
Macro for adding more sheets | Excel Discussion (Misc queries) | |||
automate creation of sheets in excel | Excel Worksheet Functions |