Free Microsoft Excel 2013 Quick Reference

Variable VBA Worksheet Name

Hi,

Can anyone tell me how to generate a variable VBA worksheet name so I
can search through the set up sheets.

Basically the VBA names of my sheets are S1 to S10 allowing me to use
the

S1.Activate etc...

How can I set up the Sheet name to be something like:

wrksheetnum = (loop through worksheet numbers)
wrksheetname = "S" & wrksheetnum

wrksheetname.activate

loop etc...

How do I get the wrksheetname bit to work?

Thanks
David


Post your answer or comment

comments powered by Disqus
I am new to creating macros so please be patient. Below is the macro I created using the record macro feature. The Activeworkbook.worksheets has a specific name listing in the code. I need to be able to run this macro on any file I open that starts with 'WPOT_input_append' is there a way to use a variable for the name listed?

Sub Test()
'
' Test Macro
'
' Keyboard Shortcut: Ctrl+m
'
Cells.Select
ActiveWorkbook.Worksheets("WPOT_input_append_1214").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WPOT_input_append_1214").Sort.SortFields.Add Key:= _
Range("BA2:BA200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("WPOT_input_append_1214").Sort
.SetRange Range("A1:BA200")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("N:AI").Select
Selection.Delete Shift:=xlToLeft
Columns("R:S").Select
Selection.Delete Shift:=xlToLeft
Columns("V:Z").Select
Selection.Delete Shift:=xlToLeft
Columns("V:V").Select
Selection.Cut
Columns("U:U").Select
Selection.Insert Shift:=xlToRight
Columns("X:X").Select
Selection.Cut

End Sub

I'm working with Excel 2003 and trying to look for a way to create some sort
of dynamic references in functions between worksheets in the same workbook.
Let me try to explain -

Say I have a workbook with 12 different worksheets with the names -
January, February, March, Etc...

On a new Worksheet I want to write a function that will use a cell as a
reference for the worksheet to use in the function.
I'm going to have a function in that cell that will return a name (In this
example - Cell A2)
In Another cell, I want to write a function that will retrieve the highest
value from a certain column (in this Example - Column C) from the worksheet
with the name in cell A2.

I was thinking that I could use a function that would look something like
this -

=max($A$2!C:C)
When Cell A2 will say - April, I'll get the heighest value from Column C on
Worksheet "April", when It'll say - June, from worksheet "June", etc...

I get an error message for this function and it will only work for me if I
use a constant value. for example -
=max(APRIL!C:C)

Is there a way to use variables for worksheet names in functions in Excel?
How should I write this function?

I'm working with Excel 2003 and trying to look for a way to create some sort
of dynamic references in functions between worksheets in the same workbook.
Let me try to explain -

Say I have a workbook with 12 different worksheets with the names -
January, February, March, Etc...

On a new Worksheet I want to write a function that will use a cell as a
reference for the worksheet to use in the function.
I'm going to have a function in that cell that will return a name (In this
example - Cell A2)
In Another cell, I want to write a function that will retrieve the highest
value from a certain column (in this Example - Column C) from the worksheet
with the name in cell A2.

I was thinking that I could use a function that would look something like
this -

=max($A$2!C:C)
When Cell A2 will say - April, I'll get the heighest value from Column C on
Worksheet "April", when It'll say - June, from worksheet "June", etc...

I get an error message for this function and it will only work for me if I
use a constant value. for example -
=max(APRIL!C:C)

Is there a way to use variables for worksheet names in functions in Excel?
How should I write this function?

Having trouble using variable in SUMPRODUCT formula...any help/comments
appreciated. I don't know how to use quotation marks (double or
single) correctly.

Dim FPsh = ActiveSheet.Name 'assign whsht name as variable FPsh
Worksheets("2005").Select

Range("E6").Formula = "=SUMPRODUCT((" & FPsh & "!$E$1:$E$1000=$C6)*" &
"(" & FPsh & "!$B$1:$B$1000>(--(""2004/12/31"")))*" & "((" & FPsh &
"!$B$1:$B$1000<(--(G$4))))*" & "(((" & FPsh & "!$J$1:$J$1000)" & ")"

Many thanks...Mike

I have a VBA code - that opens a worksheet, then gets data. The problem is
that the worksheet name can change, then it won't open. Is there anyway to
save the worksheet name as some variable, that can reference the worksheet
name/location + will automatically update?

Thanks

I need to do the following,

I have a workbook with lots of worksheets in it and each worksheet contains lots of links, for example a link named abc123456 that opens the abc123456 worksheet. The problem is that the cursor is always placed at cell A1 in the abc123456 sheet that is open. I need the cursor to stay at the same location as it was before, because I will copy and paste with vba code and need to know what row that was last copied, (that is the same as the cursor was last placed on) but i guess that is not possible when using links within workbooks like this.

So to solve this, I though I could create a variable for each worksheet that is created and store the
possition of the cursor with this code:

But, I want the "sheetrng" name to created automatically depending on the worksheet name.
For example if the worksheet name is abc123456, then the I would like the line to declare the
variable to be like this.

 or
If I can manage to do this, then I know what line I was last on and can then place the cursor at the "right" row
after following a link.

Would very much like help to solve this.
Thank you

Hello Everyone,

I now have the vba code formula thanks to Domenic.

	VB:
	
Range("B10").Select 
 
ActiveCell.FormulaR1C1 = _ 
 
"=LOOKUP(2,1/(('Worksheets(2)'!R[1]C[1]:R[42]C[1]""Total"")*('Worksheets(2)'!R[1]C[39]:R[42]C[39]>=1%)),'Planner date
6-9-2006'!R[1]C[1]:R[42]C[1])" 

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


Now do I dynamically get a different worksheet name?

I have to have this since it is imported to this workbook.

Big thanks again to Domenic and Tom.

Thanks in advance for any help on this! : D

Kurt

Any advice on the proper syntax for the below statement? This is invalid VBA code....

Code:
 
Dim wsA as Worksheet
Dim strInitials As String
 
Set wsA= Sheets("Piv_Project_" & strInitials & ")"


Hi there,

I have this formula...

=COUNTIF('Networks NW MI Report'!$G:$H,"ü")

...in B1 however the worksheet name (Networks NW MI) is in A1 and I wish the formula to link to this cell so that the worksheet name in the formula is variable. I have done this before with INDIRECT however for some reason I can't seem to structure it correctly.

Can anyone help?

Andy

I would like to find out the WorkSheet Name Of the Current work sheet so that I may check it to see if the user is on the right WorkSheet.

If variable "RIGHT" Then
I then want to make the correct sheet active using the name I just tested for.

I'd like to use the name of a worksheet, say "postage", as an input variable
in a formula on that worksheet.

For example, assume that I have a worksheet named "Postage", and assume that
'sheetName' is a variable containing the name of the worksheet.

I would want to use this formula: ="Costs of "&sheetName&":"
to create this line of text: 'Costs of Postage:'

Does someone know how to do that, or if it's even possible (preferably
without macros).

Question:

I would like to build the following formula in a VBA macro:

=IF('02 OCT'!D2="""","""",'02 Oct'!D2)"

Basically this formula will look at the contents of cell D2 on the worksheet called 02 Oct. If nothing is there, it returns nothing; if a value is there, it returns the value.

My problem is that in building the formula in VBA the name of the worksheet will change over time as I run the macro. How do I build the formula with a variable that will enter in the name of the worksheet?

Thanks,

Sean

I have several worksheets in Excel, and one is called MapIt. In "MapIt" I have linked cells (from Sheet1 worksheet) such as =Sheet1!C12. What I am trying to do in VBA is to loop through all spreadsheets(except MapIt) using MapIt as my mapping template - therefore I need to change my worksheet name in the linked formula so that I am accessing the correct sheet each time (Sheet1, Sheet2, Sheet3, ...)
Any ideas ???
The code snippet below works fine for just Sheet1 (Sheet1 is what I used to create the MapIt formulas)


	VB:
	
 Worksheets 
    With ws 
        If ws.Name  "MapIt" Then 
            txt = Join(WorksheetFunction.Transpose(Workbooks("LVjunRA_test.xls").Workshe
ets("MapIt").Range("E2:E142").Value), "") 
            Print #ff, txt 
        End If 
    End With 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Just to clarify: The Range("E2:E142").Value is the result of some other formulas in column D ... that is, column D in the MapIt sheet has the formulas with the worksheet names (=Sheet1!C12).
Thanks.
Kevin

Some time ago, I posted the inquiry below. Frank, the Excel guru, helped me with the VBA programming. However, I would like this to run automatically when a change is made in the contents of cell A1 (using Frank's reply example). So, when A1 changes, the name of the page automatically changes also.
Any ideas?
Thanks!
Kimberly

> In order to save time with dozens of identical worksheets bearing a
> similar name, I am trying to find a function that would remain in a
> cell that based upon one set of input criteria would insert a
> descriptor name into the worksheet name.
> For instance, I have 4 pages tracking production costs on 4 widgets.
> Costs are all the same, but at different levels. Using the indirect
> search function and vlookup, I can quickly update each widgets sheet
> from a master list of costs. However, I am tired of having to rename
> each sheet and the production name on it for each line with each new
> product group.
> I would like to maintain one list (with columns widget id #, widget
> name, widget line, widget cost, etc). I would like to be able to
just
> copy my production cost tracking page, input the widget id #, and the
> rest would feet automatically. All of that is not a problem with the
> exception of the WORKSHEET NAME. Can anyone please help me pull the
> widget name into the worksheet name. Like:
> Widget name: Mongo
> Worksheet name: Mongo production worksheet
> Thanks!!! Kimberly

Hi
this can only be done with VBA.
e.g.
sub foo()
activesheet.name = activesheet.range("A1").value _
& " production worksheet"
end sub

if A1 sotres your worksheet name

--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I've created a simple macro that accesses a .txt file, copies a range or data, opens a new document and pastes the data.

I need to increase the functionality of this Macro as follows:

One of the copied cells in the first document contains a value that needs to be stored into a variable (the value is always in the same cell so this reference can be hardcoded).

When the new workbook is opened I need to retrieve the value above and compare it to the worksheet names, once the relevant worksheet has been found I need to open the worksheet and paste the information into the appropriate cells.

I'm sure this can be done and am half way to doing it but can't seem to get the syntax right. If anyone can give me some pointers I'd be very appreciative. If at all possible could you provide comments for each step so I can see the processes involved for future reference.
Many thanks
Dave

Hi, can anyone help?

I am trying to use a macro to make a simple index page. If I was doing this in a worksheet I would input

=sheet1!a1

to take the contents of a1 on sheet 1 and put them into the cell on the index page.

However, I need to put this formula in where 'sheet1' is a variable defined elsewhere in the macro. I can easily put the value of the cell in, but as the contents change, I want the cells to update, therefoe I need the formula

I have been trying variations on

activecell = "=(var1)!a1"

' where var1 is the variable of the worksheet name, but it treats var1 as text and not a variable.

Any help appreciated.

In VBA within Excel, the worksheets have a name property and a (name)
property. One the user can change by right-clicking on the worksheet
tab and change the name. This is also the same name field with which i
know how to run a loop. My question is how to do i use the OTHER name
field (the only that can only be changed within the VBA properties
field) to run a loop?

The purpose is to run a macro loop regardless of the names of the
worksheet (i can't just lock off the worksheet names, they need to be
left open to change)

Any help is greatly appreciated.

Here's what I want to do:

On a Userform

Dynamically populate a listbox
with all worksheet names within
the current workbook for use
as variables.

Next, the user will, for example,
input information which will then
be entered into the appropriate
cell(s), which will be concatenated
with the above selected worksheet.

Comparable to this example, only
across multiple sheets:

http://www.exceltip.com/exceltips.ph...howtips&ID=629

You can download a spreadsheet of the above example here (VBA04-UserForms.xls):

http://www.fontstuff.com/downloads/index.htm

Example:

Worksheet (Dropdown): Housing

UserInput: Contract ID # - Value "A5D2"

Fill Cell Range: Housing!B2 with the
value "A5D2"

Since the sheet will change, I need
to be able to have the input from the
listbox for the worksheets available as
a string.

Make Sense?

Thanx.

G'Day everyone

Trying to create a Pivot on a worksheet that was created for a variable entered by user. But i am having trouble getting Excel to accept the variable as the worksheet name. (below in Red) Any ideas??

   
'Create sheet 1
    
    Set NewSheet1 = Worksheets.Add
    NewSheet1.Name = outputvar1
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "This sheet will hold the pivot for:"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = outputvar1
    
            'Creates the pivot table on sheet
                    Dim WSD As Worksheet
                    Dim PTCache As PivotCache
                    Dim PT As PivotTable
                    Dim PRange As Range
                    Dim FinalRow As Long
                    Set WSD = Worksheets(outputvar1)

                    
                    ' Delete any prior pivot tables
                    For Each PT In WSD.PivotTables
                        PT.TableRange2.Clear
                    Next PT
                    
                    ' Define input area and set up a Pivot Cache
                    FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
                    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)
                    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
                        
                    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"),
TableName:="PivotTable1")
                    PT.ManualUpdate = True
                    ' Set up the row  & column fields
                    PT.AddFields RowFields:=Array("Product", "Customer"),
ColumnFields:="Region"
                        
                    ' Set up the data fields
                    With PT.PivotFields("Revenue")
                        .Orientation = xlDataField
                        .Function = xlSum
                        .Position = 1
                    End With
                    
                    ' Calc the pivot table
                    PT.ManualUpdate = False
                    PT.ManualUpdate = True


Hi all.

Is there an "universal worksheet name", which I can use in formulas,
but independent from the actual displayed name?

E.g.
My my first sheet named "Main" contains a table to change the names of
other sheets, e.g. "Berlin" ind B2, "Paris" in B3. Finally, in a last
"Summary" sheet I want to refer to the sheets with variable names.
(Another user might work with other towns.)

So, I could use INDIRECT(Main!B2&"!C13") to find out the actual name of
a sheet from the table of the "Main" sheet. And I could use VBA to
change the tab name whenever the name is changed in B2.
BUT: If I insert a row or so in one of the sheets with variable name,
my refreing formula will not be changed automatically (in the above
example to INDIRECT(Main!B2&"!C14"). And I have to do this cumbersome
work myself.

The 2nd solution would be to use not INDIRECT but straightly Berlin!C13
and if the name is changed in B2 on "Main", e.g. from "Berlin" to
"Madrid" I could use VBA not only to change the tab name, but also to
change the formulas in sheet "Summary" to Madrid!C13. Now also the
formula would change automatically if the users inserts a new row in
the "Madrid" sheet.
BUT: As I use many formulas this would be a bit slow.

QUESTION:
If I open the sheet properties in the visual basic editor, each sheet
has not only the property "name", which is the name displayed on the
tab andthe one you can use in formulas. It also has s property "(name)"
with fixed values like "Sheet1", "Sheet2" etc. This -somehow fixed and
not displayed - name I would like to use in a refering formula.

Is this possible?
What is the exact notation?

Thank you for your kind help.
Heiner.

Hi,

Advice / help /code would be greatly appreciated.

I need to figure out how to assign the name of a worksheet to a variable. My situation is:
I have an combo box containing a list of year's, when a year is selected a new worksheet is created and renamed to the year selected. A userform containing inputboxes is then used to enter dates onto the new sheet.

I need to figure out a way of ensuring only certain dates are entered into certain sheets. (eg Only dates in 2005 can be entered into the 2005 sheet, etc etc)

The only way I have thought of doing this, (before the code fires that enters the dates onto the sheet) is to compare whatever date the user wants to enter (in the form of the inputbox) against the name of the spreadheet, I dont know how to do this, I assume that the worksheet name has be assigned to a variable before this can happen?

I would really appreciate any input /help.
Thanks
Ben

How to rename the worksheet name "Sheet1" to "File1" in VBA?
My VBA coding as follows:
Sub Copyfile()
FileCopy ("C:Data2006.xls"), C:Book1.xls
End Sub

I am trying to consolidate data from several workbooks into a single summary spreadsheet. The source workbooks are created as part of a report generation/export process. The summary workbook is an Excel 2003 file I have created myself.

I am using cell references (i.e. =C:File Location[File Name](worksheet 1)'$A$1) to extract the desired data from the source files. This is working fine except for in one case. One of the workbooks has variable worksheet names based on the date the report is run. Therefore, the formula breaks everyday because the worksheet name reference changes. Is there any way to create a variable reference to this worksheet so that whatever the date is on the worksheet tab, the formula still locates the cell reference I need? The workbook (file) name, and cell references will always be constaint.

I thought INDIRECT would do this, but all the forum entries I find seem to indicate that it only works for variable cell names, not variable worksheet names.

Hi, is there a way to change the (Name) of a worksheet by VBA Code?

I dont want to change the caption but the Name as worksheet type.

So i dont want this:
    Sheets.Add after:=Sheets(Sheets.Count)
    NewWS.Name = NameWs
I want to change their Worksheet (Name) so i can call them with that instead calling a worksheet type Variable

I know this can be done manualy at worksheet properties but, i like to know what function may change the (Name) value.


No luck finding an answer? You could always try Google.