|
Name Worksheets Based on Cell Contents
This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means that you can set your worksheets to be named by what is in cell A1 or F455 etc. You set what cell you want the name to come from in the vba code and whatever text or numbers are pulled from there become that sheets name. This is a cool way for you to have your sheets dynamically named in excel. You can even have a formula within the cell and based on what the formula spits out is what the tab will be named.
Note: For this macro to work, you need to insert it into the actual sheet where you want the name to come from a cell. You do this by going into the VBA editor by pressing "Alt + F11" and then look to the left pane. There you will see a "VBA Project" with the name of your excel file and right below that you will see a list of every sheet in that excel workbook. Double click the sheets in which you want to have this macro feature and then past the macro code in the window that pops up and you're done.
Note: To change the cell you want to be referenced for the name of the sheet simply change "a1" in the vba code into any cell reference you want. For instance, change "a1" to "b5" if you want the name to come from cell b5.
Where to install the macro: Worksheet
Name Worksheets Based on Cell Contents
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("a1").Value
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hi All,
I have my code for the most part working but unfortunately it duplicates and misses some data. I have tried to find the issue but I just can't see it and thought having some fresh eyes looking at it may be able to sort it out. Basically the source data is the orders which were sent on a particular delivery docket. The letters are not important so I have that data stripped out. I then were there were multiple orders on a line such as KC224601-12 needs to become 224601 and on the next line 224612.
Below is some sample data along with the results it gives me along with my desired results. This has not formatted properly but is also visible on the second sheet of the attached file.
HTML Code:
Source Data Actual Results Desired Results
KV224584 224584 224584
KV224585 224585 224585
KCV224587 224587 224587
ST224590 224590 224590
KC224593-5 224593-5 224593
KC224594 224594 224595
KC224596-8-9 224595 224594
KC224601-12 224596-8-9 224596
KV221101-11-2-5-28 224598 224598
KC221098-9 224601-12 224599
KCV221000 224599 224601
224612 224612
221101-11-2-5-28 221101
221111 221111
221112 221112
221115 221115
221098-9 221128
221128 221098
221099 221099
221000 221000
I have attached my spreadsheet for looking over. The part which I think is causing the error is in the code snip below which is the section which looks after the multiple orders section. This is just a snippet of the whole macro.
Code:
' If the connote has multiple orders insert a line beneath the current line and duplicate all values
If MultiOrderCheck = True Then
RightOrdersRemaining = RightOrders
x = 1
RightOrdersChecking::
For i = 1 To Len(RightOrders)
Select Case Mid(RightOrders, i, 1)
Case "-"
CurrentExtraLineOrder = Left(RightOrders, i - 1)
CurrentRightLength = Len(CurrentExtraLineOrder)
'Following messagebox is information only. Can remove once I confirm it functions
MsgBox ("CurrentExtraLineOrder = " & CurrentExtraLineOrder & " CurrentRightLength = " & CurrentRightLength)
RightOrders = Right(RightOrders, Len(RightOrders) - i) ' in preparation of the next order loop
' MsgBox ("currentextralineorder variable = " & CurrentExtraLineOrder & " New RightOrders = " & RightOrders)
LineOrder = Left(LineOrder, 6 - CurrentRightLength) & CurrentExtraLineOrder
ActiveCell.Offset(x, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(x, 0).EntireRow
Cells(r + x, "A") = LineOrder ' Paste the extra line order to the inserted line
x = x + 1 ' So that when the next order loops it gets inserted on the line beneath the last one
GoTo RightOrdersChecking ' This is so it starts to process from the start of the RightOrders
' variable again as otherwise data would be missed.
Case Else
' Not Required
End Select
Next i
'Processing of the last of RightOrders.
x = x + 1
LineOrder = Left(LineOrder, 6 - Len(RightOrders)) & RightOrders
ActiveCell.Offset(x, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(x, 0).EntireRow
Cells(r + x, "A") = LineOrder
End If
Any assistance or advice people can give would be much appreciated as it is starting to drive me nuts and I know it is probably a really simple thing I am missing.
I've got my database setup to return a score based on a series of questions. Each set of questions falls under a subject and I am trying to display both results and the category it came from. BL2:BL29 contains the final scores. a2:a29 are the list of subjects with a2 corresponding with bl2, etc. I have a formula to display the top 5 highest results from BL column. I need the names of those high scores in column a next to the score. I think column function is involved somehow but any help would be appreciated.
I work for an IT company who build their own PC's. And we use a simple Excel 2003 spreadsheet to piece-together and price-up PC's for retail, I've attached an example of the spreadsheet for you to see. It uses basic formulas so we can see at-a-glance things like VAT figures, margin, profit etc. Each PC we sell is contained within one Excel file, and each PC has it's own Workbook tab.
The problem I want to solve is whenever we change a component or price, we have to do each system manually, which gets very repetetive and time consuming.
What I'd like to be able to do is change the price of, let's say, the motherboard, and have the price be reflected across each of the PC's using that same motherboard on each seperate tab. This same system would apply when we change a product name, product ID etc.
How would I achieve this?
(SEE ATTACHED FILE)
Question:
I have a multi-worksheet set of forms in Excel. A lot of the information within the worksheets is redundant. I would like to be able to type it once, and have it auto fill through out the rest of the worksheets, or in the designated fields on other worksheets.
For example, Worksheet one, cell A1, I have "Project Number". That field appears on every form in the workbook. What is the formula/function I would enter into worksheet two, to get the project number typed into worksheet one to auto fill?
Thanks! Any and all help would be appreciated!
I would like to transfer the contents of cells in a column from one worksheet to another.
The cell, whose contents are transferred, is determined by matching other unique records between the two worksheets.
For example in both worksheets I have columns titled 'Record' and 'Date'.
The 'Date' column in worksheet A is empty. I need to fill this column with the correct dates from worksheet B.
In each cell in the 'Record' column of worksheet A is a unique value (to that column).
I want to find the cell with the same unique value in the 'Record' column of worksheet B and copy the value from the corresponding cell in the Date column (of worksheet B) to the corresponding cell in the Date column of worksheet A. Not all the record numbers in worksheet A have matching record numbers in worksheet B and the numbers are not in the same order.
Thanks for any and all help.
D
Thanks, added file as suggested.
D
A long title I know!
We currently use excel 2007 to keep record of many different makes and models of laptop recovery discs and the location of these discs.
I have been looking to make it as simple as possible for everyone to use by incorporating a search function (maybe a popup box) that will ask for the model number and return the location of the disc.
We currently deal with 13 different makes so have 13 different worksheets. each sheet has 5 colums that are used the first for part number second for model number, third for location, fourth for quantity and fifth for date added.
Can anyone help me in creating a method of searching all worksheets on column 2 and returning the contents of column 4 for the respective model?
if any further information is needed please feel free to ask.
Many thanks in advance
Hi All,
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail.
I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
Any ideas?
Thanks in advance for the help.
Cheers,
Tim
Hello.
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another.
This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at. Any help would be appreciated.
Thanks,
Andrew
|
|
