Changing A Worksheet Internal Name
Aug 4, 2009
I need to create a new worksheet and control both the internal and tab names of the worksheet. The following code works to create the new sheet, and set the external (tab) name.
Sub AddAndRenameWorksheet()
Dim ws As Worksheet
With ActiveWorkbook
.Worksheets.Add Before:=Sheets(1)
.Worksheets(1).Name = "Schedules" 'changes the external name
End With
End Sub
I do not know how to set the internal name For example, I would normally go into worksheet Properties and change the (Name) property. Is there a way to do this in VBA?
View 9 Replies
ADVERTISEMENT
Feb 9, 2013
I have an Excel file that use an internal link from one worksheet to the next. When I click on the link, I'd like it to go directly to a print preview page so I can print that one page instead just switching to the other worksheet. Is this possible?
View 3 Replies
View Related
Mar 1, 2007
I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.).
In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file.
BUT on 2 occasions this reference changed to an "external" reference to the previous month's file.
I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it.
Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time.
One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?
View 11 Replies
View Related
May 26, 2008
we have a network of small routers and wifi aps. The Ip range has got a bit out of control so I would like to be able to audit it every once and a while.
how I could go through a set of IPs on a spreadsheet, open a telnet session or whatever, try pinging each ip individually, paste a response or no response in the adjacent column then move to the next ip in the first column (some sort of loop).
Time is not too much of an issue as I could run this overnight if need be.
As a response, the macro could just copy the last line of the ping eg
Minimum = 7ms, Maximum = 9ms, Average = 7ms
if it responds
or
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)
if it does not respond
Example
Col1 col 2
Ip
1.2.3.4 Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)
4.5.6.7 Minimum = 7ms, Maximum = 9ms, Average = 7ms
5.6.7.8 Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)
6.7.8.9 Minimum = 3ms, Maximum = 21ms, Average = 9ms
etc
View 12 Replies
View Related
Mar 3, 2007
I have the following rows in my excel sheet:
-Cash needed to start the quarter.
-Net cash position.
-Begining of the quarter debt owed.
-Internal repayment.
I am trying to develop a formula for internal repayment which must fulfill following requirements(Net cash position-cash needed to start the quarter),the remaining amount goes to internal repayment but that must be <=Begining of the quarter debt owed.
View 6 Replies
View Related
May 16, 2007
Please take a look at this sample spreadsheet.
[url]
I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.
View 3 Replies
View Related
May 23, 2014
I have a workbook that need to call for external data (this is information from January till May) from another workbook to manage it. I get this external data as a Tabla_A that needs to be updated continually because the extenal workbooks is adding information constantly. However, now i have to incorporate a specific range of old information (information of December, 2013) before the external data in the same Table_A. This last information wont ever change and needs to be incorporated to analize de information from December till Now.
The problem is when I insert the December information as new arrows at the beginning of Table_A, later I update the table and this old data is remplaced automatic by the external Data range. For that reason, I'm wondering to know if there is some way to have one Table_A with a static range of arrows (december) and another range of arrows (January onwards) that is updatable.
View 1 Replies
View Related
Aug 23, 2009
I'm trying to set up a direct link to the internal excel calculator, using a button in VBA. I have the shortcut on my own toolbar, but I have many users that use the workbook, and to get them all to set up is almost impossible, so I am trying to make it simple for them.
I've tried doing it through a hyperlink direct to the calc.exe file, but get a lot of messages popping up (that'll scare a few of them). So thought I would try and find the excel name for it and use VBA, but I cannot find it anywhere.
View 2 Replies
View Related
Feb 19, 2013
Can I set the conditional formatting to highlight those cells, which contain internal (not pop-up) cell comment?
For example =10+N("free text comment"). The displayed value is 10, and I need this cell to be highlighted automatically.
The sheet has over 200 columns, so functions based on other cell values are not applicable.
View 8 Replies
View Related
Aug 5, 2003
I have a little spreadsheet aplication which ran perfectly in Office 97, but since the move to Windows/Office XP, I get the message:
Solver: An unexpected internal error occurred, or available memory was exhausted.
I'm running a P4 2.26GHz with 512MB RAM.
My code is as follows:
Public Processing_Message As String, Macro_to_Process As String
Public StartTerm As Integer, StopTerm As Integer, StepTerm As Integer
Public ResultsLabelCount As Integer
Public myErrorFound As Boolean
Public rs As Object
Public ctl As Control
View 9 Replies
View Related
Nov 3, 2008
I'm trying to get a formula to show me the value of one cell based on the value of another to automate some internal processes. Cell J3 returns today's date, then J4 says what week number that is. J5 concatenates the two to make the look up. Column A is named range "PN", and columns C onwards are named ranges to match the column heading.
What I'm wanting is for the result in test cell 2 (J8), is to return the stock requirements for the current week, based on the result of cell J5. I can do this by using nested IF statements, but that would be 8 cells to calculate, then another one to find the cell that has a value. If there a way to get Excel to recognize the value in J5 as a range name, so it just needs the one formula?
View 3 Replies
View Related
Oct 16, 2013
I am having trouble creating a hyperlink to a place in the current document for a file stored on a network drive. My workbook has a lot of sheets (50 or so), so I created a 'Navigation' sheet that contains hyperlinks to all the worksheets located therein, and pasted a link back to the Navigation sheet in cell A1 on every tab.
Setting up these Navigation sheets has never a problem until I started posting the file on a network drive. As soon as I did that, I noticed that the internal hyperlinks on all sheets in the workbook would stop working after I closed the file in Excel. Note that this network drive is not set as a drive letter on my computer, and is only accessible by entering the server ip address in Windows Explorer.
An example address the hyper is trying (and failing) to open is: "file:///\ IP ADDRESS sub-folders activefile.xlsm"
I've tried a few options, using the HYPERLINK formula, using the HYPERLINK feature in the Insert ribbon, etc, but all to no avail.
View 8 Replies
View Related
Dec 29, 2006
I have a macro that does a solver routine. I have another macro that runs this solver, then copies and pastes new inputs, then runs the solver, then copies the results and pastes them somewhere, then copies new inputs, etc. It is a long process and it worked fine a few days ago. Now when I run it, about 90% of the way through and then excel crashes. The error says:
Solver: An expected internal error occurred, or available memory was exhausted.
What can I do?
View 9 Replies
View Related
Dec 13, 2012
Trying to increment worksheet in order to summarize data from all other worksheets. I have 70+ worksheets and I'm trying to copy and paste worksheet and cell reference so that the same data on each worksheet is summarized on one sheet. Worksheets are labeled Cost (1), Cost (2), Cost (3) etc. When I copy the cell reference I can't get the worksheet name to change to the next worksheet:
=+'Cost (1)'!$E$19
=+'Cost (1)'!$E$19
=+'Cost (1)'!$E$19
I want to get this
=+'Cost (1)'!$E$19
=+'Cost (2)'!$E$19
=+'Cost (3)'!$E$19
View 2 Replies
View Related
Oct 26, 2009
when the accounts exported to excel, all the tabs shown in the file are generated. However, i need to change tab '3' to tab '10' name to the name stated in tab 'menu'. eg tab 1 need to be renamed as 'BB' (not BB/Bunut), tab 2 to 'GEN' (not GEN/General), etc. All must be capital letter.
View 5 Replies
View Related
Jan 24, 2008
I would like to change the worksheets names (many, anywhere between 50-100) to a cell (A2) value in each of the worksheets.
View 9 Replies
View Related
Apr 28, 2007
On my workbook I have autocalculation off. I'm trying to think of the best way to trigger a "Calculate" upon changing sheets. So everytime you click on a new sheet, it runs a "Calculate"?
View 2 Replies
View Related
Aug 23, 2007
I want to select a specific worksheet by it's VB name. In other words, if the sheet comes up as: Sheet3 (ESF) in the VB Editor, I would like to choose it using the Sheet3 designation. I can do it using the (ESF) designation using:
Sheets("ESF").Select
But I prefer to use Sheet3 so when the users change the name, the macro still works.
View 6 Replies
View Related
May 13, 2006
I have created a workbook. That has 35 worksheets within it. I have created a series of buttons and put in a macro code so that when the user clicks on the button it changes the active worksheet to what they clicked on.
So basically when they click button b it changes to worksheet b and if they click button c it changes to worksheet c, ETC.
The problem I have is that since it is a macro it works fine on my computer but when it is sent to other users, because of security settings on their pcs it will disable the macro [thus the buttons will not work or show up]. I have created a digital trust certificate for it. However it is alot of steps to install this for the basic user. Is there a way to create a button that will do what I want without using a macro code. In that it will be able to go to many different users and wont have to worry about the buttons not working because of security settings?
View 9 Replies
View Related
Sep 23, 2006
Using VBA, does anyone know how I can change what is selected in a listbox that is a form control on a worksheet, not on a userform?
View 5 Replies
View Related
Mar 7, 2014
How do I change the print settings of individual pages within a worksheet. Specifically, I'd like my worksheet to print out 4 pages, the first as portrait and the remaining as landscape.
View 2 Replies
View Related
Jan 14, 2010
Reference: [url]
I delete sheet1, copy sheet3, rename to sheet1 and then attempt to rename the CodeName to Sheet1 as well.
This worked initially with out errors.
Now on the first run of the macro it generates an error:
Run-time error '32813':
Method 'name' of object '_VBComponent' failed
This fails to rename the CodeName of "Sheet1" (.Name)
Run it again and it renames the CodeName but generates yet another error:
Run-time error '-2147221080 (800401a8)':
automation error
View 10 Replies
View Related
Apr 16, 2008
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.AutoFilterMode = False
Range("d6:g6"). AutoFilter
Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _
Criteria2:=Range("e2")
I've attached the file also.
What i intend to do is that : (1) if i enter a value in E3, the filter should only apply using E3 value (currently its applying E3 value but if E4 is kept blank, it takes that as = " " ) . Unfortunately, i need to have the and condition, so i have to find a way in spite of this condition. Any way out ??
(2) If i enter values in A) E3 & F3 B) E3, F3, G3...then it ahould make multiple filtering possible. But when i try to apply such a condition, the same problem as in point (1) occurs, it takes the and empty criteria range as = " "
View 8 Replies
View Related
Jun 25, 2014
i want to change the table_array reference without changing formula in every worksheet. I tried using a new worksheet and naming it the same as what is referenced in vlookup table_array but it messed up all the data.
I have to use a different worksheet every month so need a way to change reference OR how to change array data without messing up the worksheets with the vlookup
View 9 Replies
View Related
Feb 25, 2011
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
View 3 Replies
View Related
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
Sep 9, 2007
Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
View 4 Replies
View Related
Apr 21, 2012
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
View 1 Replies
View Related
Dec 23, 2013
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
View 4 Replies
View Related
Feb 14, 2012
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies
View Related