Free Microsoft Excel 2013 Quick Reference

Changing worksheet names from VB

Hi, I would like some advice on how to do the following:

I would like a workbook with say 31 worksheets.

On the last worksheet I want a list of 30 names that I can edit.

I would like some sort of easy way then that will automatically change the names of the first 30 worksheets, as well as a header on each worksheet, to match the names on the that list.

Thanks.


Post your answer or comment

comments powered by Disqus
I am trying to get a worksheet name from one workbook and add it into a cell in another workbook but cannot seem to get it to work. I am new to VBA and any help would be greatly appreciated:-


	VB:
	
 ImportClaimsDownload() 
    Dim WS As Worksheet 
    Dim cwb As String 
    Dim WB As String 
    WB = "O:SalesLogisticsWarehouseTransportCourier ReportingOperationalClaims DownloadCourierClaims.xls" 
    cwb = "O:SalesLogisticsWarehouseTransportDeliveries And ClaimsCopy of Latest Claims Accounting - TEST version.xlm" 
     'WS = activesheet "O:SalesLogisticsWarehouseTransportCourier ReportingOperationalClaims DownloadCourierClaims.xls"
     'For Each WS In wb
     'activework.Range("A2") = "HI"
    Range("A2") = WS.Name 
     ''''
     'Next
     
End Sub 

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


What I am trying to do is find the name of the worksheet that is source data for a pivot table. I know this can be done simply with

	VB:
	
PivotTable("Rpt6").SourceData 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I used a dynamic named range (called "Rpt6Rng") that uses the OFFSET function in its definition. So when I execute the above line it returns "Rpt6Rng" instead of the worksheet name and range.
Is there anyway to get the worksheet name from the named range??

Ultimately what I need is to programmatically add a row to the named range.

Thanks
-DWildman

I have created a work schedule that has to change worksheets names according to the shift

working, basically a 7 day shift showing the respective shift, in proper day order; Days,

Evenings, & Split Shift.

This is the code that I have put together, (see below), my problem is that I can't get the

code to work properly from a buttom if that buttoms are click out of order. How do I work the

"If...Then...Else" into my code so as not to create an error if the buttoms are clicked out of

order.

Sub DayShift()
Range("E6").Select
ActiveCell.FormulaR1C1 = "Days"
Sheets("TueS").Select
Sheets("TueS").Name = "FriD"
Sheets("WedS").Name = "SatD"
Sheets("ThuS").Name = "SunD"
Sheets("FriS").Name = "MonD"
Sheets("SatS").Name = "TueD"
Sheets("SunS").Name = "WedD"
Sheets("MonS").Name = "ThuD"
Sheets("FriD").Select
Range("E6").Select
End Sub
Sub EveningShift()
Range("E6").Select
ActiveCell.FormulaR1C1 = "Evenings"
Sheets("FriD").Select
Sheets("FriD").Name = "ThuE"
Sheets("SatD").Name = "FriE"
Sheets("SunD").Name = "SatE"
Sheets("MonD").Name = "SunE"
Sheets("TueD").Name = "MonE"
Sheets("WedD").Name = "TueE"
Sheets("ThuD").Name = "WedE"
Sheets("ThuE").Select
Range("E6").Select
End Sub
Sub SplitShift()
Range("E6").Select
ActiveCell.FormulaR1C1 = "Split Shift"
Sheets("ThuE").Select
Sheets("ThuE").Name = "TueS"
Sheets("FriE").Name = "WedS"
Sheets("SatE").Name = "ThuS"
Sheets("SunE").Name = "FriS"
Sheets("MonE").Name = "SatS"
Sheets("TueE").Name = "SunS"
Sheets("WedE").Name = "MonS"
Sheets("TueS").Select
Range("E6").Select
End Sub

Hope someone can help, Thanks

Hi
The following code in VB (excel 2000):
----------- start code
function f(x as double) as double
cells(1,1) = "A"
f = x + 2
end function
----------- end code

produces #ARG error when inserted into a worksheet.
The offending line is:
cells(1,1) = "A"
Does it mean I can't change worksheet cells from inside
a function ? But macros do it.
What I'm doing wrong, or is there a way around ?

Best greetings
Antek, Warsaw, Poland

--
remove SPAM before onet.pl
antekL1@poczta.SPAMonet.pl

XL2000: Formulas Calculate Incorrectly After Changing Worksheet Name

This problem may occur when the following conditions are true: Your workbook uses one or more custom functions that are written in Microsoft Visual Basic for.

Hi. Hopefully this is simple, just not simple for me to figure out:

3 Worksheets: ws1, ws2, ws3 (acutually hundreds, but for simplicity...)

ws1 has a table that pulls values from the various other worksheets

ws1:
A1="ws2" B1=ws2!A1 < gets first cell value from ws2
A2="ws3" B2=ws3!A1 < same from worksheet ws3
...
A100="ws100" B100=ws100!A1

Basically I just need a formula in column B of ws1 that gets the worksheet names from the text in column A, the "wsX!" part, not the cell reference. The part after the "!" is all relative and copies fine. It is just the part before the "!", that tells the worksheet name.

I tried things like:
CONCATENATE(B1,"!","A1")
but that just returns text.

I've put together a VBA worksheet function that has a range (called
TABLE) passed as one of the parameters.
That range is then entered into an array using loops. I've got this to
work OK if the range data is in the same sheet as I'm using the
function.
Say I'm in Sheet1 of my workbook and the data for TABLE is on Sheet2
I'd like to be able to input
=TEST('Sheet2'!A1:F10)
VBA doesnt seem to like this
I got round it by adding the parameter SN to allow the range data to be
on a different sheet. That has to be manually input in inverted commas
right now.
So my function now looks like:-

Function Test(SN As String, Table As Range)

Dim zarray() As Single '
Dim norows As Integer ' number of rows in the array
Dim nocols As Integer ' and the number of columns
Dim Row As Integer ' Will be the first row of the range
Dim Col As Integer ' will be the first column of the range

' Establish the number of rows and columns in the range "Table"
norows = Table.Rows.Count
nocols = Table.Columns.Count

ReDim zarray(norows, nocols) As Single

Row = Table.Row - 1
Col = Table.Column - 1

For i = 1 To norows
For j = 1 To nocols
zarray(i, j) = Sheets(SN).Cells(i + Row, j + Col)
Next j
Next i

Is there any way I can isolate the worksheet name from the Range
parameter so that I can drop the SN parameter?

Best regards

RPH

--
RPH

Hi,

Last time, DonkeyOte wrote a macro for me for changing worksheet name. Now the worksheets sequence have changed and so, the macro required slight amendment to reflect this change. I tried to amend it without success. I wish someone can assist me to amend the script to suit the current workbook.

Sub x()
    Dim i As Long
    Dim sWks As String
    
    On Error Resume Next
    For i = 3 To 32
        sWks = Worksheets("Menu").Range("D5").Offset(i).Text
        If InStr(sWks, "/") Then sWks = Left(sWks, InStr(sWks, "/") - 1)
        sWks = WorksheetFunction.Trim(sWks)
        Worksheets(i).Name = sWks
    Next i
End Sub
Now, a new worksheet "Worksheet 1" is added to the 2nd worksheet. so basically, i need to those names shown in "Menu" to be named in the worksheet eg worksheet 3 to be named as "!", worksheet 4 as "AIR" etc.

Thanks alot.

Hi,

Would someone please help me to write a marco for changing worksheet name? I attached a file here.

Basically, 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.

Please help.

Thanks

I've put together a VBA worksheet function that has a range (called
TABLE) passed as one of the parameters.
That range is then entered into an array using loops. I've got this to
work OK if the range data is in the same sheet as I'm using the
function.
Say I'm in Sheet1 of my workbook and the data for TABLE is on Sheet2
I'd like to be able to input
=TEST('Sheet2'!A1:F10)
VBA doesnt seem to like this
I got round it by adding the parameter SN to allow the range data to be
on a different sheet. That has to be manually input in inverted commas
right now.
So my function now looks like:-

Function Test(SN As String, Table As Range)

Dim zarray() As Single '
Dim norows As Integer ' number of rows in the array
Dim nocols As Integer ' and the number of columns
Dim Row As Integer ' Will be the first row of the range
Dim Col As Integer ' will be the first column of the range

' Establish the number of rows and columns in the range "Table"
norows = Table.Rows.Count
nocols = Table.Columns.Count

ReDim zarray(norows, nocols) As Single

Row = Table.Row - 1
Col = Table.Column - 1

For i = 1 To norows
For j = 1 To nocols
zarray(i, j) = Sheets(SN).Cells(i + Row, j + Col)
Next j
Next i

Is there any way I can isolate the worksheet name from the Range
parameter so that I can drop the SN parameter?

Best regards

RPH

--
RPH

Hi, all.

I have a couple of different hypothesized scenarios about automatically changing the worksheet name. Please view below.

Is it possible to change the worksheet name based on a cell value in another worksheet?

i.e.: worksheet 2 name = A6 of worksheet 1

Is it possible to change the worksheet name based on a formula solution in a cell from the same worksheet?

i.e.: worksheet 4 name = A5 of worksheet 4

I have a number of worksheets between a worksheet titled "Start" and one titled "End", does anyone know what code I could use to change the names of these worksheets to the text found in cell A1 of each worksheet?

Thanks

Hi to all my friends on the board! My search did not find this, so if it's on the board, please accept my regrets and point me on my way.

I have a six-sheet workbook tracking ticket sales for a show, one sheet for each day of the show. First column name, second column number of tickets sold, third column total purchase.

Each worksheet name is the date of the show and total tickets sold. Ex: "Fri 2 - 6" is for Friday the 2nd with 6 tickets sold so far.

Can I get the "6" to update from a cell that's tracking the total at the bottom of the column? (ie. "=sum(b2:b14)) so when I add another line for two more tickets, the name tab will change to Fri 2 - 8?

Thanks in advance

Hi,
I want to input a column of formulas where the part of the formula which changes on each row from the row above is the worksheet name. I want the cell ref to remain the same

ie

formula in B6
='WC - 23-03-09'!J2

formula in B7
='WC - 30-03-09'!J2

The sequence of the worksheets should follow the sequence as they appear left to right in the worksheet bar at the bottom of the page.

I was looking for a way to "fill" in the rest of the column where its the worksheet which changes sequentially in the formula rather than the cell ref

Anybody have any ideas.

here is my code to save the current worksheet as a text file

.....
Sheets("Transaction File").Select
ActiveWorkbook.SaveAs Filename:= _
"C:UPLOADNCAS" & Format(Date, "yyyymmdd") & ".txt",
FileFormat:=xlTextPrinter, CreateBackup:=False

The problem is that when the code executes, the Transaction File
worksheet is renamed with whatever name is given to the file that is
being created. I need the Transaction File worksheet to remain named
Transaction File because the next time the code is run, it looks for
that worksheet.

How can I keep the code from changing the worksheet name? Thanks!!!!

Hi

How can i change the name of a checkbox inserted in a sheet, with vba code?

Tanks

I have a master list of 260 different listings. I also have a seperate worksheet that is a form for each of these listings data in a more individualized way. What I am trying to do is create 260 different worksheets that were copied from the form worksheet and named from a list of names on the master. I then want to populate each of those sheets with the corresponding data from the master. Please help. Thanks

Afternoon all,

Dave has helped to get me this far, I can find a file on c: drive, I can isolate the path name etc, my next challenge is to list all the worksheet names (eg Sheet1, Sheet 2 etc) from the file while it is still closed. The code so far is......

Sub FindIt()
Dim filnam
Dim filpatnam
filnam = [a1] ' Determine what file is required

With Application.FileSearch
.NewSearch
.LookIn = "C:"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Filename = filnam
If .Execute > 0 Then
MsgBox Replace(.FoundFiles(1), filnam, ""), , "BPL"
[a2] = Replace(.FoundFiles(1), filnam, "") & filnam
Else
MsgBox "File Not Found", , "BPL"
End If
End With
' lock down file path and name
filpatnam = Replace(.FoundFiles(1), filnam, "") & filnam
' list the worksheet names
[code to loop thru' file, collect names and copy to cells b1:b'x']
End Sub

Does this make sense?

TIA,

GrahamB

Question is below the sample worksheet data

Store . . . . . . Sales . . . . . . . . . . Formula For Sales Column

Store #1 . . . $1,000,000 . . . . . . . =’Store #1’!$B$5
Store #2 . . . $1,500,000 . . . . . . . =’Store #2’!$B$5
Store #3 . . . $1,250,000 . . . . . . . =’Store #3’!$B$5
Store #4 . . . $3,000,000 . . . . . . . =’Store #4’!$B$5

The above worksheet reads Sales data from another worksheet within the spreadsheet. The worksheet names are Store #1, Store #2, etc.

When a new store is added, I have been copying the formula to a new cell. Then, I edit the formula to change the worksheet name.

What I want to be able to do is include a reference in the formula that reads the worksheet name from the store column.

The method below works, but is cumbersome.

Skeleton Text Formula: =”=’”&A1&”’!$B$5”
.......The above formula results in a text string in the cell --> ='Store #1'!$B$5

Do the following when a new store is added:
:Copy Skeleton Text Formula to new cell
:Copy, Paste Special, Value
:.......(result displays as text)
:F2 (edit cell), but make no changes
:Press Enter - Proper Sales results are displayed

CELL function returns the filename and worksheet name of the current worksheet.

Any ideas?

Thanks so much!
Dean

Hi,

I would like to change the worksheets names (many, anywhere between 50-100) to a cell (A2) value in each of the worksheets.

Thank you so much for your help!!!

JP

I would like to automatically (macro) create a new worksheet and have it's name assigned from a cell in another work sheet. Both worksheets will be in the same workbook.

The cell is I52 within a worksheet named "Cover".

I did find some old posts on similar subjects, but I did not have any luck in implementing.

Thanks for the help in advance.

Danny

HI All

I need to change a worksheet in the link I have created.

eg

In Cell A1 formula is:

=('C:dataworksheetsteam[quality Scores.xls]Jan'!B2)

What I want to do is have a drop down list by Mth in B1 which will
dynamically change the Worksheet name to whatever month is selected in
B1.

So if B1 = Mar

Then A1 will chnage to:
=('C:dataworksheetsteam[quality Scores.xls]Mar'!B2)

--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=378886

I have a file with multiple worksheets (one for each day). I want to change
the name of each worksheet to the name of the month.

For example, I want to change 'Nov' to 'Dec' in all of the worksheets in the
file.

When I use the Replace function, it says it can't find the data it is
looking for.

Thanks

I need to create a list of worksheet names (tabs) or all the files (I'd
like the file names as well, but think I've already found the add in
from http://www.tushar-mehta.com/excel/software/index.html that does
that part)

the purpose is to create a list - from which I will use concatenate to
make formulas linking to all the worksheets (they are all formated the
same) so I can summarize financial information. (I use concatenate)
make massive page of formulas and past special values to another page
(creating text that upon adding an = sign in front will become
formuals) I've found this works well on pages with 8,000 or so
formulas.

so in the end I'd like to have at least a file name and all sheet names
for that file - I can add the drive and folder and any formula specific
formatting like "[" etc

D:FY05.Mar[Excel.Filename.xls]sheet1'

D:FY05.Mar[Excel.Filename.xls]sheet2'
D:FY05.Mar[Excel.Filename.xls]sheet3'
D:FY05.Mar[Excel.Filename.xls]sheet4'
D:FY05.Mar[Excel.Filename2.xls]sheet1'
D:FY05.Mar[Excel.Filename2.xls]sheet2'

thanks, Drew


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