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


ADVERTISEMENT

Rename Part Of String From Combobox

Sep 29, 2006

I have a Userform (PictureViewer) with a.o. a combobox and a textbox on it. The combobox has a picturename with the full path, i.e. C:My DocumentsJohnMy PicsSummer VacationImg_0001.jpg. The textbox will have the replacement name for that picture entered in it while the picture is visible, i.e. Harry and Antoinette (no extension). Pictures get cycled on this Userform with a forward and backward button. When I click the forward button, the next picture opens up and the name of that picture replaces the previous name in the combobox. What I would like to do is on the click of a commandbutton, copy the string (full path) in the combobox to a column (A), starting at the first cell and in the column beside it (B) end up with the new picture name including the full path. In this case, that would be C:My DocumentsJohnMy PicsSummer VacationHarry and Antoinette.jpg. The next picture name I want to change would be in the cells below this. This could go on for many pictures. I have the macro to change the file names once when I have cycled through everything I want to change. Is it possible to do this in one click or do I have to split the drive name, all folder names and picture name, replace the picture name with the new name manually and concatenate it again? The depth of folders can vary of course. The macro for renaming is as follows

Sub RenameTheFiles()
Dim OldName As String
Dim NewName As String
Dim LastRow As Long
Dim I As Long
LastRow = Range("A65536").End(xlUp).Row
For I = 1 To LastRow
OldName = Range("A" & I).Value
NewName = Range("B" & I).Value
Name OldName As NewName
Next I
End Sub

View 9 Replies View Related

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 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 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:


sht01.name="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").Select
Sheets("Sheet5").Name = "Sheet1"
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Sheet2"
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "Sheet3"
Sheets("Sheet8").Select
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
01
02
03
04

Number of .csv
05
06
07
08

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 sheet1.name = "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

Unhiding Sheets With Combobox Selection And Duplicating Sheets Automatically

Sep 27, 2011

I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.

The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.

View 9 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 ":".

VB:
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)

VB:
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
Range("A7:M7").Select
With Selection
.WrapText = False
.MergeCells = False

[Code] ......

CallSample2.xls

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.

Eg.
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

Combobox-sheets Combination

Sep 20, 2006

I have a form (userform) which has a combo box and this combo box functions as a selector. I mean, when the person chooses one of the items (sheet1, sheet2, sheet3, etc.) inside the combo box, it views the sheet he/she picked. How will I do that thing? What code/s will I write in my module?

View 4 Replies View Related

Linking Sheets To ActiveX Combobox In Excel

Sep 13, 2012

I have a name list on sheet2, column A that will have names added to and removed from. Everytime this list is modified it sorts in ascending order and creates a new sheet named after the name added. These items are added to an ActiveX combo box on sheet1. I need to know how to link the item in the combo box to the corresponding sheet(ex: item: "Jim", sheet name: "Jim").

View 2 Replies View Related

VBA - Delete Sheets In Active Workbook According To Combobox Value

May 8, 2014

That particular question has been solved, but now i need it to work with multiple values from combobox.

So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7

The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"

WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.

Run-time error '-2147417848 (80010108)': Automation error

The object invoked has disconnected from its clients.

When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate

View 5 Replies View Related

Editing Combobox VBA To Work With 2 Sheets Instead Of Same Sheet

Jul 16, 2014

Problem- I need a combobox on sheet1 to reference a column in sheet2 and return only a distinct list.

[Code].....

If I put my sheet2 column info on sheet1, and then reference it appropriately as built in the code above, it code works great. So I know it's close. I cannot change it to work with 2 sheets though. I've tried to "set wsSheet2 = wbBook.Worksheets("Sheet2")" and call it 'where I think it's appropriate', which is the problem. I don't know what's appropriate.

changing the steps in the code above, where in my case the combobox is on sheet1, and the range/data referenced is on sheet2?

This is what I've changed the original VBA to, and when I run the debug, it picks up the last "with...combobox1" statement as the error (error 1004, app/object defined error):

[Code] ......

View 1 Replies View Related

Auto Open - Sheets Array And Combobox?

Jun 7, 2011

I am currently using this code to place names in a combobox (active X). How can I use an array to perform each line to all 12 months without repeating the code over and over?Here is what I have:

Code:
Sub auto_Open()
Sheets("FEB").ComboBox1.List = Array("ALL", "ACT", "ROF", "MM")
Sheets("FEB").ComboBox2.List = Array("ACTvsROF_2", "ACTvsPLN_2", "ACTvsLY_2", "ROFvsMM_2", "ROFvsLM_2", [code].....

View 2 Replies View Related

Hide/Show Sheets Chosen In ComboBox

Feb 9, 2010

Sub ComboBox1_Chg()
For Each Sheet In Worksheets
If Sheet. Name <> "CoverPage" And Sheet.Name <> Sheets("CoverPage").ComboBox1 Then
Sheet.Visible = False
Else: Sheet.Visible = True
End If
Next Sheet
End Sub

It works if I step through it (F8) but the ComboBox doesn't work. It's named ComboBox1, and in the properties the ListFillRange shows all of the names in the list in the ComboBox correctly.

View 5 Replies View Related

Macro To Unhide / Hide Sheets With Combobox Selection

Jun 18, 2007

I have a workbook that contains approx 50 sheets and will grow to somewhere in the region of 200.

The majority of sheets, which contain the raw data referenced by the renaining sheets, are hidden. I will occasionally need to update the data in some of those hidden sheets and would like an easy / quick way of unhiding them.

The front page has several comboboxes which select the page needed for the calculation being performed, eg I select Chapter2 in the first combobox, section 4 in the second and page 12 in the 3rd. The output is combined / abbreviated into into a cell eg Ch2-Sec4-P12. That being the name of a sheet I then use INDIRECTs to retrieve the data I want and place it in a spare sheet, that works well.

I'd like to do the same to select the sheet to unhide. I can setup the comboboxes to give the name of the sheet I want to unhide / hide in a cell but then I'm stuck;

How can I use the contents of a cell in place of the sheet name in a macro command such as Sheets("data").Visible = Not (Sheets("data").Visible) ?

View 9 Replies View Related

VBA Export Data To Path Depends Upon 1 Combobox Values Sheets And 2 Textbox Date Values

Jun 5, 2014

I have set of user-form contains with Combox & 2 textbox and to generate report one cmd button

I have 3 different sheet contains report of daily activities ( Dispatch,Closed,Cancel)

If Dispatchcalls Select In Combobox1 ,Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from dispatchcalls Then Save Data Into Excel File As "Dispatchcalls".

If Closedcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Closedcalls Save Data Into Excel File As "Closedcalls".

If Cancelcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Cancelcalls Save Data Into Excel File As "Cancelcalls".

"C:UsersmaniDesktopNew folderLenvo_ReportsONSITE CasesVlokupuf" This is path i stored existing 3 file dispath,closed,cancel

View 3 Replies View Related

Populating Combobox 2 With Items That Match Criteria From Combobox 1

Mar 30, 2009

Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.

if column a = bears and column b = colours of bears then

when I select bears in combobox one, combobox 2 would populate with colors of bear.

I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.

I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.

View 9 Replies View Related

Auto Populate 2nd Combobox Based On 1st Combobox Data

Mar 30, 2014

I need to populate two combo boxes from excel sheet, the data will be like below:

Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102

So from the above data, one combo box should hold unique values A & B.

On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.

So the data should be like below:

If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.

Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved