Create New Sheets And Rename VBA

Jul 24, 2012

I have a worksheet titled "master plan" with many columns of data. I want to create many tabs based on this data.
One tab I want that just has the data from columns C, F, A, E, G and L (in that order) Starting with the header data in row 2. And titled "LOB".

Then I want a different tab for each unique item in Row C with these same columns (C, F, A, E, G and L from "master plan" tab or A, B, C, D, E and F from new "LOB" tab). The tab name should be the unique row C value.

So for example, say that there about 20 rows where 'A' is in column C, about 30 with 'B' in column 'C' etc .... There should be a tab with the name 'A' with those 20 rows of data and a tab with the name 'B' with the 30 rows with B and so on.

I also would prefer not to have to delete the existing 'A' and 'B' tabs every time before recording the macro so if it can either create a new tab or replace an existing tab with that name if it already exists.

To make things a little more difficult.. for the (in this example) 20 items with an 'A' in column C, there are (at this time) 3 different possible items in column B of 'master plan'. I'd like to create 3 separate tabs for each unique value in column B and I want the name to be dependent on the data in Column B (for example, the three unique items in column B with a column C of 'A' are Red, Green and yellow. I want three new tabs set up for each and the tab names to be: if B = Red, then tab name = 'Stop', If B = Green, then tab name = 'Go', if B = Yellow, then tab name = 'Slow'.

I have something that partially works, but I have to create the 'LOB' tab first and it doesn't work if any of the sheets already exist. And it doesn't do the Red, Green, Yellow part.

Here is what I currently have:

Sub DeptTabs2()
Dim strSrcSheet As String
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngCell As Range
Dim strLastDept As String
Dim intDestRow As Integer
On Error GoTo ErrHnd

[Code] ..

View 6 Replies


How To Create A Macro To Add New Worksheet And Rename It

Aug 25, 2009

I tried recording a macro to add to a new worksheet, but it gives error while running.

Issue as I understand is, by default excel gives a new worksheet a name (Say Sheet 4), and when u run macro worksheet, new created name may be different.

View 7 Replies View Related

How To Create Macro To Add New Worksheet And Rename It

Oct 3, 2012

I need to have many users press a button within the excel sheet that duplicates the form from the current page, opening a new tab (copying the form) that also prompts to input a specific name e.g. "Enter date and day or night shift" to name the tab itself.

I have just worked out how to put a Command Button in and used the above code to create the new page assigning the code to it.

I need the "my sheet" to be prompted and customisable.

View 3 Replies View Related

Create Copy Of Worksheet & Rename

Dec 21, 2006

I have the following code in my vb app. It creates the new worksheet, gives it the corect name, copies all of the existing data from an existing worksheet and pastes it in the new worksheet, but I can not get it to refresh the formulas. When I look at the formulas in the new sheet they reference back to the MasterSheet worksheet(which is my template I copy and paste from when making a new worksheet)

objExcel.Sheets("Sheet1").Name = MySheetName
objExcel.Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
objExcel.Range("B1").Value = Me.DTPicker10.Value
objExcel.Range("B1").Value = Me.DTPicker10.Value
objExcel.Application.Run "LoadDataFromAccessUsingDates"
objExcel.Visible = True

View 3 Replies View Related

Create Macro To Rename Worksheets In Other Folders

Dec 23, 2008

I have created an appointment schedule spreadsheet. Once I get the spreadsheet running smoothly, I would like to create a worksheet for every day of the year that we are open.

I have decided to have one main folder with 26 subfolders in it. In each of those 26 subfolders, there will be one workbook with 12 worksheets in it. That will be two weeks worth of appointments as we are open Mon-Sat. Of course I want to name the worksheet tabs at the bottom of the workbook according to the appropriate calendar date. Then there is also a cell at the top of each page that also has the date, the same as the date on the tab. Just wondering if there is a simple way to create a macro to rename all these worksheet tabs, or if I have to physically open up each workbook, and rename all the worksheet tabs according to the calendar date. Then once the worksheet tab is named, can you make it automatically put the same date into Cell A1?

View 9 Replies View Related

Create Input Message Box To Rename Sheet?

May 18, 2012

im trying to create an input message box to rename a sheet.

So far I have

Dim strPrompt As String
Dim strTitle As String
Dim iRet As Integer
'create a copy


But I seem to be getting a complie error with it...I am trying to simply rename a worksheet to what has been put in the message box

View 2 Replies View Related

Rename Sheets - But Not Every Sheet

May 2, 2008

I have 20 sheets in the workbook. 10 sheets are named YR1, YR2, etc. Once the user enters information on sheet "Assumptions" - names are setup and linked onto each YR page. So user enters CY10, which links to cell C5 on YR1 to show CY10.

I would like a macro that renames just the YR1, YR2, ....sheets, to CY10, CY11, .... I do not want any of the other sheets to change names.

(FYi - Using Excel 2003)

View 9 Replies View Related

Rename All Selected Sheets (For Each)

Apr 8, 2009

how I can change this code to apply to only the sheets that are selected (grouped) as opposed to every sheet in the workbook?

For Each shName In ActiveWorkbook.Sheets

View 9 Replies View Related

Rename Sheets Using ComboBox

Sep 4, 2006

in the WorkSheet_Activate for Sheet 3, which changes the sheet name to the ComboBox Value. Is it possible to use the default name of the Sheet, in this case Sheet 3, if the ComboBox Value = "0". I have the following codes for a ComboBox & Userform:

Private Sub ComboBox1_Change()
Sheet3. Range("E1").Value = ComboBox1.Value
End Sub


Private Sub Userform Initialize()
ComboBox1.AddItem 0
For x = 1 To 60 Step 1
ComboBox1.AddItem Format(x, "0")
Next x
ComboBox1.Value = Sheet3.Range("E1").Value

I also have this code ..............

View 2 Replies View Related

Rename Sheets In VBA Editor

Oct 13, 2006

is there a quick and easy way to rename the Sheets In VBA Editor (they are currently all mixed up), i.e. I have Sheet221 followed by Sheet11 etc. Is there a way to reorganise them so that I get Sheet01 followed by Sheet02 etc.

View 5 Replies View Related

Rename Sheets From A List

Apr 22, 2007

i am looking for a macro that renames existing worksheets from multiple lists and have the new name appear in cell b1 of each new sheet.

List one: Worksheet name "Stats", cells b7:b20
Worksheets i would like renamed: Sheet9:Sheet22

List two: Worksheet name "Stats", cells 26:b34
Worksheets i would like renamed: Sheet48:Sheet56

View 7 Replies View Related

Copy Sheets & Rename

Dec 29, 2007

I have a workbook containing many worksheets. The worksheets are clients in my system.

I have no big problems with my VBA code, but what I really thought was simple is causing my system to take very long time processing.

Sheets("Kunde mal").Select
Sheets("Kunde mal").Copy Before:=Sheets(3)
Sheets("Kunde mal (2)").Select
navn = Range("kundeNavn").value
ID = Range("kliNr").value
Sheets("Kunde mal (2)").name = navn & " - " & ID
Sheets("Kunde mal").Select

Why is this taking so many seconds?

Are there other ways to copy sheets?

View 6 Replies View Related

Rename Sheets In For Next Loop

Jun 18, 2008

I have a WorkBook with many Sheets "imagine Sheets.count=50"

9 of the CodeNames for this Workbook's sheets are: sht01. sht02, sht03, sht04, sht05, sht06...

If I want to change the name "not the CodeName" of say "sht01" I can use:"New Name"

but is there a way of doing this whitin a For Next this way:

For X = 1 To 9
Sheets("sht" & X.CodeName) = X ' the Name X is just for this eg
Next X

View 9 Replies View Related

Rename The Sheets According To The Current Time

Jan 13, 2008

how to rename the sheets according to the current time 1201pm, 1202pm etc. i need a macro.

View 9 Replies View Related

VBA- To Rename The Sheets Directly After Creating Them

Aug 21, 2008

I have a macro which creates 4 worksheets. Unfortunately sometimes they are named by default "Sheet5", "Sheet6", "Sheet7", and "Sheet8" when the macro is set to "Sheet1", "Sheet2", "Sheet3", "Sheet4". My question, is there a way to rename the sheets directly after creating them so that the sheet in the very first position in the spreadsheet is named "Sheet1" (the same would go for sheets 2-4). The code below wont work if the sheet decides to be a name other then "Sheet5" so how do I point to the the worksheet known as Sheet5 without using its name and tell excel I want its default to be "Sheet1"?

Code below:

Sheets("Sheet5").Name = "Sheet1"
Sheets("Sheet6").Name = "Sheet2"
Sheets("Sheet7").Name = "Sheet3"
Sheets("Sheet8").Name = "Sheet4"

View 9 Replies View Related

Rename Sheets-Worksheets Codename

Oct 31, 2006

I'm tring to create a newsheet and rename the codename but it doesnt seem to be working. Here is my
Private Sub Update_Button_Click()

AddYear = YearBox.Value

WFName = "WF Tracker SITE " + AddYear
wf = "WF_Edin_" + AddYear

Sheets.Add After:=WF_Template
ActiveSheet. Name = WFName
Sheets(WFName).CodeName = wf 'This line wont work
End Sub

View 9 Replies View Related

Rename Sheets By Using Dynamic Loop For Rows?

Jan 30, 2014

In column C I got some data like this:

Number of .csv

Number of .csv

Notice that there is an empty cell in between.The data starts at C12 up to C21. The data is in Sheet2.There are also 8 more sheets( Sheet3 to Sheet10). I want to rename each sheet, starting from Sheet3 according to each cell. For example the Sheet3 to be renamed to 01, Sheet4 to 02.

What I can do is something like this:

[Code] .....

And repeat this code for every block of data I got by changing everytime the i and the a. But this method is not so optimized because there are cases that the number of rows for each block is not the same and I have to change everytime the i counter. Is there any way to do 1 loop for all the sheets using maybe Worksheets.Count and another dynamic loop for the rows ? The data always start every 6 rows eg( C12, C18 etc). Also I was thinking to define an integer representing the number of rows for the loop...

View 6 Replies View Related

Selectively Rename Sheets By Sheet Code Name?

May 9, 2012

How do I selectively rename sheets by sheet code name?

Instead of = "New Name" I need sheet1 to be a variable of a specific sheet.

View 4 Replies View Related

Rename Sheets Based On TextBox Values

Aug 24, 2006

Say you have a userform with 10 TextBoxes, let’s call them TxtN1…TxtN10 and your workbook has 10 sheets, say “Sheet1”…”Sheet10”. Now if one wants to code a procedure to rename those worksheets when the user hits a command button it’s simple enough.

Sub Rename_click()
For i = 1 To 10
With Sheets(“Sheet” & i )
. name = TxtN & i
End With
Next i
End Sub

But now if the user then changes the textboxes TxtN1…TxtN10 to a set of new strings it’s obvious that the following coding segment will not work:

For i = 1 To 10
With Sheets(TxtN & i)
.name = TxtN & i
End With
Next i

It goes without saying that the program will try to select the worksheets with the new name (and they obviously don’t exist yet)…

View 6 Replies View Related

Macro To Create And Rename Multiple Worksheet And Then Placing Relevant Data

Feb 24, 2014

I want to find a way to create multiple worksheets and matching data to be placed on appropriate sheets.

Here are more details (Please check the attached sheet screenshot as well):


In a workbook, there is a "Master-Sheet". This master sheet contains 8 columns.

I want to create as many new worksheets after master sheet as the values are there in column B (Column 2 i.e. Ad Group). In above attached screenshot, there are 8 values (B2:B9 or A-H). So, I would like to create 8 new worksheets after the master sheet. Also, I want to rename them based on their value from Ad Group column.

Each newly created worksheet should have same columns as the master sheet . Same 8 columns with their name intact.

Finally, I want all matching data of the Ad Group values to be placed on their respective newly created worksheet. For example, worksheet A should have A2:H2 data. Worksheet B should have A3:H3 data, and so on.

Please note that same ad group may have more than one row data. But I don't want to create multiple worksheet of same name ad group. The worksheet should be just one, but all matching data should be placed in that one sheet.

I know it is a bit complex task, but I am sure there would be a way to perform this automatically - probably a macro.

View 1 Replies View Related

Rename 500 Sheets Based On Modified Cell Value On Each Sheet

Jul 31, 2013

I receive a workbook each day with approx 500 tabs of call data and I would like to relabel all the tabs with the user names. The user names are in a merged range "A7:M7". I'm having difficulty getting the following script to rename the sheets. Currently the values in "A7:M7" look like "User: ADAM ENGEMANN-558".

I am trying to delete each occurrence of "User: " while naming the sheets as well as removing the merged range before doing so that the names are in A7 only. The sheet names should look like "ADAM ENGEMANN-558", or, ideally just "ADAM ENGEMANN". For some reason my code will not modify the cells. (See attached example)

This works if I manually unmerge the cells before running and remove the ":".

Sub RenameSheets2()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Range("A7").Value <> "" Then

[Code] .....

This his how I modified it but it doesn't work on the supplied sheets. Please note that if I manually insert some blank sheets into my workbook and merge the cells etc it does work. There seems to be something odd going on with the sheets they are giving me. (they aren't protected)

Sub RenameSheets2()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Range("A7").Value <> "" Then

I tried adding the following to remove the merged ranges and remove 'User: ' from the names
With Selection
.WrapText = False
.MergeCells = False

[Code] ......


View 3 Replies View Related

Rename Tabs (Sheets) Based On Cell Contents

Nov 10, 2009

I have 5 sheets. In the first sheet I have set up 4 cells where I want the contents of them to automatically rename the other 4 corresponing sheets.

In Sheet 1, Cell B6, I want the contents (which will be text) to be the name of sheet 2 automatically.

In Sheet 1, Cell B7, I want the contents (which will be text) to be the name of sheet 3 automatically.

View 8 Replies View Related

Copy Worksheet, Rename As Cell Value & Sort All Sheets

Oct 1, 2009

I am working with a workbook that has data automatically entered each time a new child is enrolled to the program. The child's data is automatically entered to the sheet named "Intake". What I need to do now is rename that sheet using the child's name as the sheet name, but also keep the sheet named "Intake" for the next entry. I would then like to sort the sheets alphabetically but leaving the "Intake" sheet either as the first sheet or the last sheet. I have attached an example of the workbook I am working with.

View 6 Replies View Related

Copy/Move Rename Sheets Based On List

Aug 11, 2006

I have a list of names in sheet1, starting with cell A3. I would like to copy sheet3, insert it after sheet3 and rename the sheet to correspond to the names in the list.

If i have 30 names I would like 30 sheets. If i add a name, I would like to repeat the copy, insert and rename steps for the extra names as i add them.

View 7 Replies View Related

Input In Cell Triggers Macro To Create Duplicate From Template Sheet And Rename?

Jan 22, 2014

I am currently doing an excel database of students application. I want a macro that creates a duplicate of a template sheet and renaming it based on the name of the student. Meaning once a name appears in the name cell, a sheet of that particular name is automatically created. At the same time, i want all the data regarding the applying student to appear the newly created sheet as shown in the attached file.

View 4 Replies View Related

VBA Create Sheets With Buttons That Hyperlink To Different Sheets?

May 13, 2013

I run into is that many of our staff don't know there are tabs at the bottom of the page that show different worksheets. writing some VBA code that would grab the names of all the sheets and create a single sheet with the tabs listed as buttons that would hyperlink to each sheet of it's given name. Kind iof an index for the sheets.

Also bonus if 3 rows could be insterted on each of those pages with a button that links back to the index page.

View 1 Replies View Related

Rename Sheet - Hide It - Create Copy Of Same Sheet

Dec 21, 2011

I have about 100 products and each has its own sheet that I fill with data. Once I have finished with a sheet I rename it and create a copy and then hide the original and delete the entered data from before and start over.

Is there a way I can make it so when I hide a sheet it will automatically create a copy and delete a specific range?

View 1 Replies View Related

Create Sheets From Ranges

Oct 16, 2009

I have this file, I would like to create a sheet for each different supplier code (column D), and each sheet named with the supplier code.

View 3 Replies View Related

Create Multiple Sheets

Jul 16, 2009

i have a master Sheet which have 15000 Entries (A:A15000) many are Duplicate, Unique is 63 . Now i want to make the 63 Sheets in a Same Workbook as per these Entries.

I record the Macro for the first 7 Sheets. Now how can I Modifiy this Macro to 63, Instead of record again for another Entries.

Actually, I have recevied a File some time it has 15000 Company Names sometime 20000 and sometimes 2000, Now i want to Make the Sheets according to Company Name Wise.

Now , i know how to record the Macro, but i want to know how to Modifiy the Macro.

" How to Learn and Modifiy the Macros"

My code

Sub CreateSheets()
' Macro1 Macro
' Macro recorded 7/16/2009 by AAditya
' Keyboard Shortcut: Ctrl+w

View 9 Replies View Related

Create Range Across Sheets

Feb 26, 2008

This is more acedemic than anything, but I'm trying to create a range that refers to ranges on more than one sheet... is this possible? "Union" doesnt appear to work when the ranges are on different sheets. Netither of the Set My_Range3 statements work below.

Option Explicit
Public Sub test()
Dim My_Range1 As range
Dim My_Range2 As range
Dim My_Range3 As range
Set My_Range1 = Worksheets(1).range("A1:A10")
Set My_Range2 = Worksheets(2).range("B1:B10")
'Set My_Range3 = Union(My_Range1, My_Range2)
'Set My_Range3 = range(My_Range1, My_Range2)
My_Range3.Interior.ColorIndex = 3
End Sub

Both Set My_Range3 statements will work if the ranges are on the same worksheet.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved