Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Vba Question: How To Rename Sheets With A Keyword In Them?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'm running a macro to rename sheets within a book and I'm currently using the 'for each ws in worksheets' command. This isn't really correct though as I'd like to only rename sheets which are named Node X (As in Node 1, Node 2, etc). So I guess the easiest thing would be to put as the first item in the loop: if = "Node " & X then

The trouble is I'm not sure how to do the X part! Any pointers?! Thanks in advance!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

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
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Delete Empty or 'Broken' Named Ranges (#REF!)
- This macro will delete named ranges which refer to empty or #REF! ranges. This means that if a named range has been 'br
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
- List all of the named ranges in a workbook in Excel and the corresponding values stored within every named range. This

Similar Topics

Would like to use XL to create a connected graph ( finance calls it a
binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.

XY scatter plots points OK. Need to connect points (nodes)--each node except
for t=T has two lines coming out of it.
A 3-peiod example is shown below. Eventually need to generalilze.

Data is
reference T Value
NOde 1... 0 100
NODE 2... 1 110
NODE 3... 1 90
NODE 4... 2 121
NODE 5... 2 100
NODE 6... 2 81

want a line to connect NODE 1 to NODE 2
want a line to connect NODE 1 to NODE 3
want a line to connect NODE 2 to NODE 4
want a line to connect NODE 2 to NODE 5
want a line to connect NODE 3 to NODE 5
want a line to connect NODE 3 to NODE 6

Suggestions would be greatly appreciated.


I want a button that can rename worksheets based on their name. I am thinking it would need a string compare but I'm not sure how to do that.
The button should rename anything named "Node (then a number)" or "Sheet(then a number)" and then rename them to "Node (then a number)" based on the order they're placed in the workbook.

I am trying to total 2 columns when the data in both columns match Row 6 and Row 16 are the same Row 5 and Row 19 are the same.

What Formula do I need in cell C2

******** ******************** ************************************************************************> Microsoft Excel - Book2 ___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 Work Sheet Row No     2 Hydraulics Node 39 1   3 Hydraulics Node 41 1   4 Water Node 7 1   5 Water Node 9 1   6 Water Node 15 1   7 Water Node 23 1   8 Pneumatics Node 7 1   9 Pneumatics Node 9 1   10 Pneumatics Node 10 1   11 Pneumatics Node 16 1   12 Pneumatics Node 17 1   13 Pneumatics Node 21 1   14 Water Node 20 1   15 LH 4 Button Door Panel 14 1   16 Water Node 15 2   17 LH 4 Button Door Panel 9 1   18 Pedal Link Node 12 1   19 Water Node 9 2   20 Pedal Link Node 18 1   Sheet1  
[HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name box

I've developed a workbook which acts as a sort of database, using one sheet as the entry point. Each time the user creates a new entry, VBA copies a template sheet (hidden) and names it according to the entry number on the first sheet; Node 1, Node 2, Node 3 etc.

This all works fine, and I've got referencing working and various other functions. I'm just trying to put a delete function in though and I'm a little confused as to how to go about it.

Basically, if I press the 'Delete Node' command button on the data entry sheet, it should lookup that Node number (e.g. 3), and delete that sheet - I've got this part working ok. But it should then shuffle all of the numbers after 3 down by one, so it would need to first identify how many sheets there are named Node x with numbers above 3, and rename them all as Node x-1, whilst also changing the number in cell (4, 3) on each sheet to x-1 as well.

How could this be done?

I'm currently using this sort of formula to look up each sheet:


Sheets("Node " & Sheets("Data_Entry").Range("Nodenumber")).Select

So the 'Delete' script looks something like this at the moment:

Sub DeleteNode()
If MsgBox("Delete This Node?", vbYesNo, "Discard Changes") = vbYes Then
GoTo Delete
MsgBox ("Deletion Cancelled")
Exit Sub
End If
Application.ScreenUpdating = False
Call Unhide
If MsgBox("Are you sure?", vbYesNo, "Discard Changes") = vbYes Then
Sheets("Node " & Sheets("Data_Entry").Range("Nodenumber")).Select
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Sheets("Calcs").Range("CurrentNodes").Value = Sheets("Calcs").Range("CurrentNodes").Value - 1
Sheets("Data_Entry").Range("Nodenumber").Value = Sheets("Data_Entry").Range("Nodenumber").Value - 1
Sheets("Data_Entry").Range("Date").Value = ""
Sheets("Data_Entry").Range("Node_Description").Value = ""
Sheets("Data_Entry").Range("Node_Design_Intention").Value = ""
Sheets("Data_Entry").Range("Drawings_Used").Value = ""
On Error GoTo Skip
Sheets("Data_Entry").Range("SelectNode").Value = Sheets("Data_Entry").Range("Nodenumber").Value
Call SelectNode
Call hide
Application.ScreenUpdating = True
MsgBox ("Node Deleted")
Exit Sub
MsgBox ("Deletion Cancelled")
End If
End Sub

Any help would be really appreciated!

hi all,

i got stuck in a situation which led me to come here for help.
i have an excel sheet which contains node connected each other and i have to draw it either on excel or visio.

User label node A node B
a-b a b
b-c b c
c-d c d
d-b d b

pls help as data is very large.


Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node 1 to node n using the dim statement. I tried running a for loop:


for i = 1 to n
dim node&i as BNode ' this is a Netica specific object
end for

however VB doesn't seem to like my node&i

Can you help?




I'm trying to write a macro to map the xml folder elements on different worksheets, But the problem is i have to map individual columns against the xml schema elements, I need a macro that could check the worksheet name and map the particular node to that sheet at one go... please help .


I need a row number to automatically increment as I copy a formula horizontally across columns. Sample spreadsheet downloadable he

Or viewable as a Google Doc he

To elaborate, the formula in question starts in Column T. In T2, the formula compares Node 1 to itself - hence the zero. In T2, it compares Node 1 to Node 2, in T3, Node 1 to Node 3, and so on. Column U does the same thing, except it compares Node 2 to Node 1, Node 2 to Node 2, Node 2 to Node 3, and so on. I want to copy out my formula so that all 50 nodes in the matrix are filled in, in this manner.

The formula increments properly when copied vertically. Take a look at the representative portion of the formula right after the ABS in cell T2: ($C$2-$C2). The formula stays "anchored" on Node 1 via the $C$2. The second part, $C2, increments as you copy it down, to $C3, $C4 and so on. (The same is true for the rest of the formula - it just goes ($D $2-$D2), ($E$2-$E2) and so on.)

Now take a look at Column U. The "anchor" changes from $C$2 to $C$3. That's because Node 2 is now the basis for comparison. In Column V, the anchor changes to $C$4, because Node 3 is the basis for comparison.

The thing is, the anchors in Columns U and V were updated by hand. That is to say, if you copy the formula in Column T one cell to the right, nothing increments. I realize that this is "proper" Excel behavior - when you copy formulas horizontally, generally only the column will increment. But I would really love a way to force the row number of the anchor to automatically increment as I copy the formula to the right. (Note that the anchor has to retain the $ before the row number because when I copy the formula down, that portion of the formula must stay fixed.)

Please let me know if you need any further clarifications. And if you have any suggestions, I would be very grateful. Thank you.


I am trying to create a macros which will tell me which nodes I have to join together with a line in a irregular mesh (Lines need to join nodes vertically and horizontally). For example here is a typical mesh:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152

So for example (for horizontal lines) ,

-ex. node 1 is joined to node 2
-ex. Node 7 os joined to node 8

The output I desire would be in two colums, with the starting node in one column and the corresponding node in the next column.

-ex 1 2
2 3
3 4

I have written a macros, but it doesnt work, and I can't figure out why, see below,

Sub Nodes()
J = 0
For x = 1 To ((Worksheets("Sheet1").Cells(2, 1).Value) - 1)

If Worksheets("Sheet1").Cells(22, x) "" Then

Worksheets("Sheet1").Cells((J + 39), 1).Value = Worksheets("Sheet1").Cells(22, x)



x = x + 1

Loop Until (Worksheets("Sheet1").Cells(22, x).Value) = ((Worksheets("Sheet1").Cells((J + 39), 1).Value) + 1)

Worksheets("Sheet1").Cells((J + 39), 1).Value = Worksheets("Sheet1").Cells(22, x)

End If

J = J + 1

Next x
End Sub

Thanks in advance for any help!!


I want to set the parent nodes to the equivalent of CheckBox1.Value = Null if >0 and < All Child nodes are selected.

The syntax for changing the value of individual nodes seems to be NodeObject.Checked = True but I cannot find a way to set a third (greyed out) state.
Here is some of the code:


Private Sub FlagKeyWordsTreeView_NodeCheck(ByVal Node As MSComctlLib.Node)
Dim CKIDS As Integer
Dim I As Integer
Dim AI As Integer
Dim BI As Integer
Dim X As Integer
    CKIDS = 0
    If Node.Children = 0 Then
    '    If Node.Checked = False Then Node.Parent.Checked = False
        I = Node.Parent.Children
        Set A = FlagKeyWordsTreeView.Nodes(Node.Index).FirstSibling
        Set B = FlagKeyWordsTreeView.Nodes(Node.Index).LastSibling
        AI = A.Index
        BI = B.Index
        For X = AI To BI
            If FlagKeyWordsTreeView.Nodes(X).Checked = True Then
                CKIDS = CKIDS + 1
                If CKIDS = I Then
                    Node.Parent.Checked = True
                ElseIf CKIDS > 1 Then
                    Node.Parent.Checked = False         ' want to put NULL here
                    Node.Parent.Checked = False
                End If
            End If
        Next X
        Set A = FlagKeyWordsTreeView.Nodes(Node.Index + 1).FirstSibling
        Set B = FlagKeyWordsTreeView.Nodes(Node.Index + 1).LastSibling
        AI = A.Index
        BI = B.Index
        For X = AI To BI
           FlagKeyWordsTreeView.Nodes(X).Checked = Node.Checked
        Next X
    End If
End Sub

Hi All,

i was wonderig if anyone knows how to count the number of parents of a treeview node?

I 'm trying to work out what "Level" each node is at i.e. if there are 3 parents to a node it is at level 4 and so on?

Hope that's clear guys

any help is greatly appreciated and i'll defo add to your rep


I have a spreadsheet with about 10 org charts on it. I created these via Insert=>Diagram=>Organizational Chart.

On each sheet I also have a list of positions. I would now like to create some VBA code to enable me to do the following.

When clicking on a cell in the list VBA inserts a node just below the master node with the cell content being linked to the inserted node.
Unfortunately I cannot even seem to get VBA to insert the node let alone fill the text in.

Can anybody out there help at all?

Hi all, Im reviewing some macros and user forms within a spreadsheet, and attempting to work out how the original author has created some functionality

A number of items are defined as nodes. i.e. "Dim strWhatever As node"

When attempting to extract the forms to another sheet, and run code to set them up, I get told "User-defined type not defined", which I assume means that "node" is defined somewhere within the existing VBA, but I can't find it in the existing file

Some code that is falling over is as follows:

Private Sub NodeCheck(ByVal node As MSComctlLib.node)
Call doCheckChildren(node)
Call doCheckParent(node)
End Sub

When checking, the very first line is highlighted

Does anyone have any idea what this means?!

Hello, I hv been encountering this problem whenever i assigned the variable "Node" where the system prompted "User-defined object not defined".

Do I need to manually create the Node object in the object library? or Can point me out how to solve this. Thanks in advance.

All data is copied in the first column i.e. A
Below is a sample of data that I need to work with. it is comprised of text and other weird format. The numbers that I am interested in have a space 0. example - "89.45 0"
In these figures, I need to find the values that come below a cell having value - failed.
below the cell that has failed, will always be a cell having value - node this is.
now I need to count the number of rows of decimals that are coming below the failed cell.
for example, using the below data, the data will be pasted starting from cell A1. Below the first "failed" cell value, comes "the node this is" cell, below this cell are the decimal numbers that have failed. unfortunately there is no clear end to the failed data except that the cell "node this is" is repeated. this will mark the end of the data that needs to be copied to a different location. The "failed" value can be repeated in the column, and I need the cells having the decimal formats.

To make things simple and sum up the requirement. I need the values that come under "Failed" and that is starting after "node this is" and ending before the next " node this is"

89.45 0
and there is
node this is
76.98 0
32.78 0
12.89 0
here is
node this is
98.56 0
node this is
43.89 0
56.65 0
node this is


I have a worksheet and I have named some ranges for some cells in the in, worksheet. (I did it by right click on the cell and choose Name a Range.... and gave a name for the cell.)

Now i have a xml which contains nodes with the same name as the name of the range. Now I want to populate values in the sheets cell from the xml for each range that is named sheet .

For eg:



Cells in the worksheet have a namerange as bookid and bookname.
Now for those cells i want to store 100, abcd respectively.

I can for loop each node in the xml , but i dont want to hardcode bookid, bookname in the for each loop for the node name and also range name, eg


Set list = xmlDoc.DocumentElement.SelectNodes("Booklist")
For Each node In list
 Source.Worksheets("Books").Range("bookid").Value = node.SelectSingleNode("bookid").Text

How can i achieve this??

Lets Just say that the work book name is...

CSM B-12 Node 1.xls

I Have one set of code doing the following..


Dim txt As String
txt = ThisWorkbook.Name
WorkbookName = Left(txt, Len(txt) - 4)
Sheets("Summary").Range("AL2") = WorkbookName 'column AL

Which returns CSM B-12..perfect...

Then i have a a formula that does...



Which returns

CSM 12

also perfect...semi...

What i would like to do is bring this into the said macro to return in...

In AL2 = CSM B-12 (Removing Node 1 from the Name of the workbook Name)
In AL3 = CSM 12 (removing B- and Node 1)
In AL4 = 1 (Removing everything but keeping the 1 from the end)

Id like to get the above to work in the macro vs. formulas...Ideas??

Thank you


Is there a way to get innerText from all childnodes of a node quickly? I am using VBA.

A web page has a node "H1" which has several childnodes, some of them are "p" and some are "STRONG". I need to get the text in these nodes. I tried to use getelementsbytagname and getattribute methods, but it is getting too complex. Is there an easy way?



Hi All,

I have a code which helps me to downloads only the files with specific extensions like xls, xlsx, ppt etc from outlook, Earlier it use to download all attachments but still I have following issues which are still outstanding and I need help for fixing this issues.

1) Download the files from multiple subfolders .i.e. select the folder in tree view and use that selection in main macro to download attachments.
2) Download only the latest files.

I have selected the true for checkboxes in the property window of treeview1 so that we can select the multiple folders.

I also got the below code to get the selected folders of outlook as selection for downloading the attachments from them. I need help to incorporate this so that we can fix the issue no.1


Private Sub TreeView1_NodeCheck(ByVal Node As MSComctlLib.Node) 
    Dim n As Node 
    If Node.Parent Is Nothing Then 
        Set n = Node.Child 
        Do Until n Is Nothing 
            n.Checked = Node.Checked 
            Set n = n.Next 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I am attaching my updated macro file for your reference. please have a look.

Thanks a lot for your help in advance.

Hi all,

I have a computer program here which chucks out thousands of lines of data in a report text file, and I need to extract some data from it.

If I copy and paste the relevant bit, I get lots of lines similar to this one (where the text is in one single cell in each row):


   128     node  dist = 0 elev = -597 temp = 13.7

Basically what I would like to do, is write a macro that can scan through all of the lines in a loop, and if the line contains the phrase "node dist =" then I would like it to extract 3 numbers and deposit them in three separate columns the numbers are "Node Distance", "Elevation", and "Temperature", which in this case are "0", "-597" and "13.7".

If the line contains the phrase "node dist", then the format of the line will always be the same (the number at the beginning (128) is just the line number). So I guess the macro would need to look for each number (including any - signs) after an "=". Is this possible? If so, how on earth do I go about trying to do it??

Thanks in advance!


Hello I am new to this forum so please bear with me...

I am looking for help in developing steps in a spreadsheet to calculate a sum based on a few conditions. The basis of my spreadsheet works fine, however I am trying to add the contents of a cell to another by checking other cells.

For example:

Cells A1:A10 contain a numerical sequence (1-10 respectively) to indicate a "Node"

Cells B1:B10 contain a user selected number (1-10) which defines the Node it is fed from. We'll call this "Fed From Node" If Cell B2=1, that means that Node 2 is fed from Node 1.

Cells C1:C10 contain a value which is input by the user. We'll call this "Amps"

Cells D1:D10 would show the total "Amps" of the current cell added to all others that are fed from same.

What I am ultimately trying to figure out is how to add the "Amps" to each "Node" and show the results in column D.

More specifically if the spreadshet looks like the following:

Node-----Fed From Node-----Amps-------Total

How would I add the "Amps" from Node5 to Node4 and Node4 to Node2 to achieve a total of 22.5 IF Node5 is fed from Node4 and Node4 is fed from Node2?

I have tried macros and different excel formulas for the past few days and I am really stumped.

Any help would be greatly appreciated.


I need to Count no. of cells having common text string at start and then varying later. The data is like this in a single column and may run into hundreds of rows,

Splice at node: 13637605_X86 Part Number: 7209-5299-0W
Left Hand Side C.S.A. = 4.850000 Total C.S.A. = 4.850000 C.S.A. = 0.000000 Right Hand Side
Page: 2
Splice at node: 13653204_X16 Part Number: BLUNT_CUT_ASSY
Splice at node: 13746734_X16 Part Number: BLUNT_CUT_ASSY
Page: 3
Splice at node: 19153416_A96 Part Number: BLUNT_CUT_ASSY
Splice at node: 1_13235988_A Part Number: BLUNT_CUT_ASSY
Page: 4
Splice at node: 1_13637605_X Part Number: 7209-5299-0W
Left Hand Side C.S.A. = 3.500000 Total C.S.A. = 3.500000 C.S.A. = 0.000000 Right Hand Side
Splice at node: 1_19153416_A Part Number: BLUNT_CUT_ASSY
Page: 5
Splice at node: 2_13637605_X Part Number: 7209-5299-0W
Left Hand Side C.S.A. = 5.000000 Total C.S.A. = 5.000000 C.S.A. = 0.000000 Right Hand Side

Splice at node: 13637605_X86 Part Number: 7209-5299-0W

I need a formula is to count the cells which start with text string "Splice at node:",

Thanks in advance,


I'm trying to write to a worksheet and I'm not sure how to make the code position the active cell one cell above the "NODE"(defined name), insert a new row, and then write the data from the userform to the appropriate columns in that new row.
So far, I've got this - it works but always writes the userform values one cell BELOW the "NODE"!!!


 RowCount = Worksheets("Sheet1").Range("NODE").CurrentRegion.Rows.Count
          With Worksheets("Sheet1").Range("NODE")
               .Offset(RowCount, 0).Value = Me.txtFirstName.Value
               .Offset(RowCount, 1).Value = Me.txtLastName.Value
               .Offset(RowCount, 2).Value = Me.cboDepartment.Value
               .Offset(RowCount, 3).Value = DateValue(Me.txtDate.Value)
               .Offset(RowCount, 4).Value = Me.txtAmount.Value
               .Offset(RowCount, 5).Value = Me.txtDescription.Value 
          End With 

Any suggestions on how to select the "NODE", go up one row in the same column, and then write the data?

Thank you!


I'm at a loss figuring out how to set the color of a specific node within a
shape created from BuildFreeform. It's been converted to a shape. I'm looking
up the index of a value in a range, and want to set the color of the
corresponding node in the shape. Any help is much appreciated.

Hello all,

I am trying to create a truss solving program. I would like the user to define the number of nodes in the truss and give coordinates for each node (i.e. node 1, node 2, etc). Then the user will create the truss members by defining their connecting nodes (i.e. member 1 is node 1 to node 2). I would like to create a plot of the truss to show the user what he or she is inputting. Is there a way to define the nodes as coordinates so that I could simply create another series of the nodes. I hope what I am describing is not too confusing. I have attached a small sample in which I want the four nodes to be connected according to the user-defined left and right nodes (i and j joints in spreadsheet).