Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting and naming a tab with vba - any help please thank you
I have excel 2007, I have a list of products in column A rows 5 to 19 ( with
possible new additions) and I need a macro to insert tabs and name its with the product names. Everytime the macro is run it will identify the new prroducts and will create a tav for it. Each tab will be a copy or the master tab (where I have the formulas and calculations) and will copy the name of the tab in Cell B1. I appreciate any help. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting and naming a tab with vba - any help please thank you
First you want to defined a dynamic named range (if possible) for the
column that contains the product list. Then you want a macro to loop through the range and check if there is an existing worksheet with that name. If not, create the worksheet and name it with the product name, copy over the information from the master worksheet, and copy the name of the worksheet into cell B1. Do you have any code written at all? --JP On Oct 20, 8:44*am, Eduardo wrote: I have excel 2007, I have a list of products in *column A rows 5 to 19 ( with possible new additions) and I need a macro to insert tabs and name its with the product names. Everytime the macro is run it will identify the new prroducts and will create a tav for it. Each tab will be a copy or the master tab (where I have the formulas and calculations) and will copy the name of the tab in Cell B1. I appreciate any help. thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting and naming a tab with vba - any help please thank yo
Hi JP thank you for answering,
I tried the one as follow for creating a tab but is not working. I got this code from the comunity, maybe the problem is the excel version. Is giving an error message when it's run Run-Time error 9 - Subscript out of range Sub Macro2() ' ' Macro2 Macro ' With Sheets("sheet1") 'where list is in col A For i = .Cells(.Rows.Count, "a").End(xlUp).Row To 2 Step -1 Sheets.Add.Name = .Cells(i, "a") Next End With ' End Sub "JP" wrote: First you want to defined a dynamic named range (if possible) for the column that contains the product list. Then you want a macro to loop through the range and check if there is an existing worksheet with that name. If not, create the worksheet and name it with the product name, copy over the information from the master worksheet, and copy the name of the worksheet into cell B1. Do you have any code written at all? --JP On Oct 20, 8:44 am, Eduardo wrote: I have excel 2007, I have a list of products in column A rows 5 to 19 ( with possible new additions) and I need a macro to insert tabs and name its with the product names. Everytime the macro is run it will identify the new prroducts and will create a tav for it. Each tab will be a copy or the master tab (where I have the formulas and calculations) and will copy the name of the tab in Cell B1. I appreciate any help. thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting and naming a tab with vba - any help please thank yo
Hi,
A couple of assumptions. The sheet you want to use as a template is called "Base Data" change this to suit. The list of worksheets you want creating is in column A of a sheet called "Menu" Also change these to suit. Right click any sheet tab, view code and paste this in and run it Sub stance() Sheets("Base Data").UsedRange.Copy Dim ws As Worksheet Dim myrange As Range Lastrow = Sheets("Menu").Cells(Cells.Rows.Count, "A").End(xlUp).Row Set myrange = Sheets("Menu").Range("A1:A" & Lastrow) For Each c In myrange On Error Resume Next Set ws = Sheets(c.Value) If ws Is Nothing Then Sheets.Add.Name = c.Value Sheets(c.Value).Range("A1").PasteSpecial Paste:=xlPasteAll End If Next End Sub Mike "Eduardo" wrote: Hi JP thank you for answering, I tried the one as follow for creating a tab but is not working. I got this code from the comunity, maybe the problem is the excel version. Is giving an error message when it's run Run-Time error 9 - Subscript out of range Sub Macro2() ' ' Macro2 Macro ' With Sheets("sheet1") 'where list is in col A For i = .Cells(.Rows.Count, "a").End(xlUp).Row To 2 Step -1 Sheets.Add.Name = .Cells(i, "a") Next End With ' End Sub "JP" wrote: First you want to defined a dynamic named range (if possible) for the column that contains the product list. Then you want a macro to loop through the range and check if there is an existing worksheet with that name. If not, create the worksheet and name it with the product name, copy over the information from the master worksheet, and copy the name of the worksheet into cell B1. Do you have any code written at all? --JP On Oct 20, 8:44 am, Eduardo wrote: I have excel 2007, I have a list of products in column A rows 5 to 19 ( with possible new additions) and I need a macro to insert tabs and name its with the product names. Everytime the macro is run it will identify the new prroducts and will create a tav for it. Each tab will be a copy or the master tab (where I have the formulas and calculations) and will copy the name of the tab in Cell B1. I appreciate any help. thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting and naming a tab with vba - any help please thank yo
Hi Mike,
That worked except that some of the sheets were named with # instead of the name. As follow is the list of products 1Add-On Tools 2Custom Solutions 3SAP 4Env Geo Apps/Tools 5Maintenance 6Min Ex Apps/Tools 7Oil & Gas Apps/Tools 8Training 9TSA 10Uxo Apps/Tools 11Geosoft Oasis montaj 12Metech acQuire 13Geosoft DAP 14Geosoft Target 15Services the number at the beginning was entered by me just to tell you which ones the tab was not named. That were 10 - 7 - 6 - 4 Thank you "Mike H" wrote: Hi, A couple of assumptions. The sheet you want to use as a template is called "Base Data" change this to suit. The list of worksheets you want creating is in column A of a sheet called "Menu" Also change these to suit. Right click any sheet tab, view code and paste this in and run it Sub stance() Sheets("Base Data").UsedRange.Copy Dim ws As Worksheet Dim myrange As Range Lastrow = Sheets("Menu").Cells(Cells.Rows.Count, "A").End(xlUp).Row Set myrange = Sheets("Menu").Range("A1:A" & Lastrow) For Each c In myrange On Error Resume Next Set ws = Sheets(c.Value) If ws Is Nothing Then Sheets.Add.Name = c.Value Sheets(c.Value).Range("A1").PasteSpecial Paste:=xlPasteAll End If Next End Sub Mike "Eduardo" wrote: Hi JP thank you for answering, I tried the one as follow for creating a tab but is not working. I got this code from the comunity, maybe the problem is the excel version. Is giving an error message when it's run Run-Time error 9 - Subscript out of range Sub Macro2() ' ' Macro2 Macro ' With Sheets("sheet1") 'where list is in col A For i = .Cells(.Rows.Count, "a").End(xlUp).Row To 2 Step -1 Sheets.Add.Name = .Cells(i, "a") Next End With ' End Sub "JP" wrote: First you want to defined a dynamic named range (if possible) for the column that contains the product list. Then you want a macro to loop through the range and check if there is an existing worksheet with that name. If not, create the worksheet and name it with the product name, copy over the information from the master worksheet, and copy the name of the worksheet into cell B1. Do you have any code written at all? --JP On Oct 20, 8:44 am, Eduardo wrote: I have excel 2007, I have a list of products in column A rows 5 to 19 ( with possible new additions) and I need a macro to insert tabs and name its with the product names. Everytime the macro is run it will identify the new prroducts and will create a tav for it. Each tab will be a copy or the master tab (where I have the formulas and calculations) and will copy the name of the tab in Cell B1. I appreciate any help. thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
inserting and naming a tab with vba - any help please thank yo
Hi Mike,
From my last post I figured out what was the problem, (special characters like / )and fixed it, however when I add new products and try to run the macro again it doesn't create a tab for the new products. Can you help me. Thank you "Mike H" wrote: Hi, A couple of assumptions. The sheet you want to use as a template is called "Base Data" change this to suit. The list of worksheets you want creating is in column A of a sheet called "Menu" Also change these to suit. Right click any sheet tab, view code and paste this in and run it Sub stance() Sheets("Base Data").UsedRange.Copy Dim ws As Worksheet Dim myrange As Range Lastrow = Sheets("Menu").Cells(Cells.Rows.Count, "A").End(xlUp).Row Set myrange = Sheets("Menu").Range("A1:A" & Lastrow) For Each c In myrange On Error Resume Next Set ws = Sheets(c.Value) If ws Is Nothing Then Sheets.Add.Name = c.Value Sheets(c.Value).Range("A1").PasteSpecial Paste:=xlPasteAll End If Next End Sub Mike "Eduardo" wrote: Hi JP thank you for answering, I tried the one as follow for creating a tab but is not working. I got this code from the comunity, maybe the problem is the excel version. Is giving an error message when it's run Run-Time error 9 - Subscript out of range Sub Macro2() ' ' Macro2 Macro ' With Sheets("sheet1") 'where list is in col A For i = .Cells(.Rows.Count, "a").End(xlUp).Row To 2 Step -1 Sheets.Add.Name = .Cells(i, "a") Next End With ' End Sub "JP" wrote: First you want to defined a dynamic named range (if possible) for the column that contains the product list. Then you want a macro to loop through the range and check if there is an existing worksheet with that name. If not, create the worksheet and name it with the product name, copy over the information from the master worksheet, and copy the name of the worksheet into cell B1. Do you have any code written at all? --JP On Oct 20, 8:44 am, Eduardo wrote: I have excel 2007, I have a list of products in column A rows 5 to 19 ( with possible new additions) and I need a macro to insert tabs and name its with the product names. Everytime the macro is run it will identify the new prroducts and will create a tav for it. Each tab will be a copy or the master tab (where I have the formulas and calculations) and will copy the name of the tab in Cell B1. I appreciate any help. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a quick way of inserting and naming multiple worksheets in Excel? | Excel Worksheet Functions | |||
inserting and naming range | Excel Programming | |||
Tab Naming | Excel Worksheet Functions | |||
naming using F5 key | Excel Programming | |||
Inserting Sheets and Naming | Excel Programming |