Free Microsoft Excel 2013 Quick Reference

Excel VBA Active WorkSheet Name

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.


Post your answer or comment

comments powered by Disqus
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

I am trying to determine the active worksheet name in case an operator activates the macro and they are not on the correct worksheet.

I am trying to replace a worksheet name in a formula linked to a different workbook using Excel 2010.

Current: ='M:ACCOUNTINGMike2010 Sales6 - Period 20106152010[3 - BoulderWE061510.xls]Weekly Sales'!$E$46

Need to Replace with:='M:ACCOUNTINGMike2010 Sales7 - Period 20106222010[3 - BoulderWE062210.xls]Weekly Sales'!$E$46

ok so here is my issue. Using Excel 2003 and/or 2007 I simply would use Replace 0615 with 0622 I would have to hit esc and it would give me a #REF! error. Then I would replace 6 - Period with 7 - Period and presto it was updated. Now using Excel 2010 when I try to esc it stops.

Anyone?

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 am having trouble getting this code to work. I have an open
workbook, with an active worksheet named "Sheet1" which has an embeded
chart (object) here is my code that runs when you press the
CommandButton1 on the user form I created:

Private Sub CommandButton1_Click()
Call Create_GIF
Image1.Picture = LoadPicture("c:Mychart.gif")
End Sub

Sub Create_GIF()
With Worksheets("Sheet1").ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "1995 Rainfall Totals by Month"
.Export Filename:="layth.gif", FilterName:="GIF"
End With

The code works until the .export line, the Chart get a new name as I
specefied in the line .ChartTitle.......
but I recieve the following error message:
RUN TIME ERROR '1004':
Application-defined or Object-defined error

Where am I going wrong here. I tried changing the filename to "layth"
, I also tried using jpg instead, but nothing worked.

Can someone help PLEASE.

More questions ... more help needed, please.

I want to create a lookup value from the active worksheet name.

So if the active sheet is called "Sales", I want to get out the text string "sales".

Current formula is =VLOOKUP(("Sales"&A7),sd,5,FALSE) - fine for the Sales sheet, but I need to use it for a whole load of other sheets.

I know it can be done ... :x

In old versions of Excel, the maximum Worksheet Name length is 31 characters (inc. blanks). Does this change in any later version of Excel? I am using a software package which renames Worksheets to a Title which can be any length (usually max 70 characters).

Hello.

I want to insert the current active worksheet name into one cell in the active worksheet.
How do I do that?
I guess it might be something like =[worksheet......] but am I wrong.

Please help?

Hi all:

Part of my macro includes the following:

Worksheets("Yesterday's").Activate
Cells.Select
Selection.ClearContents
Instead of putting the worksheet name, how can I reference the worksheet by the worksheet number. (the vba editor has a sheet # for each worksheet) E.g. Excel identifies this particular sheet "Yesterday's" as sheet 19, how will the code change? is it possible to do this at all?

Thanks a lot.

Hello, I am trying to use a very simple VBA macro to recalculate a range in a non-active worksheet without recalculating everything...I basically try the following:


	VB:
	
Worksheets("Calc").Range ("BH38:CC47") 
Application.Run "OnKeyCalculateSelection" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which returns "object does not support this property or method"

and


	VB:
	
Worksheets("Calc").Range ("BH38:CC47").Select 
Application.Run "OnKeyCalculateSelection" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which says "Select method of clas range failed"

if i try the two pieces of code above using the vba name of the sheet ("sheet1") instead of "calc" i have the error "Subscript out of range"

can anyone help me with that? I've been spending almost 2 days on it now...

I am wondering if someone could possibly help me as I am an amateur to Excel.
I can do what I require using incell formulas but would like to do it using VBA code.
I have Monthly Worksheets and wish to copy the complete range B6:U1493 to the active Worksheet when the Month I wish to use is selected from a Drop Down list of Worksheet Names.
All the cell ranges in all the Worksheets including the Active Worksheet are the same (B6:U1493)

Any help would be greatly appreciated

Regards

Steve

I am very new to excel VBA. I have a unique problem of copying data from multiple files.
Here it goes, I have a master file named "master.xlsm" and in that file I have a worksheet named "MyData". In that worksheet, I have column G that contains the file names upto row 20(last available data row). One example of a file name is Myfile1.xls. Each of these files have several worksheets, but the names of worksheets that needs to be opened (and data copied) are written in column H. So for example Myfile1.xls and its corresponding worksheet to be used (say "saving1") sits side by side in column G and H. All the files are in one folder.
I can write simple macros, but this seems diffcult to me. I need to copy a range of data from the relevant worksheets and consolidate it in a single worksheet in the master file. The data has to be copied below each sets of data from the worksheets in the order of column H.
As I am very new to VBA, I will appreciate, if the code has some comments that helps me understand it.
Thanks very much

Hi
This is my first venture into Excel vba, so apologies for the poor layout and technique!

I am trying to collect all the names of the spreadsheets that fail to unlock into an array,then present them as a msgbox at the end of the routine.

I am not getting this array thing at all at the moment, so time to ask for help.
Thanks for taking time to view.

Cheers

Oldrat


	VB:
	
 Worksheet 
For Each wSheet In Worksheets 
    On Error Resume Next 
     
    wSheet.Unprotect Password:=pword1 
    If Err.Number  0 Then wSheet.Unprotect Password:=pword2 
    If Err.Number  0 Then Ans = MsgBox(wSheet.name & vbCrLf & "WorkSheet Failed to Unlock!" & vbCrLf & "Continue?", vbYesNo +
vbInformation) 
     
    If Ans = vbYes Then Goto Worm 
    If Ans = vbNo Then Exit Sub 
    Err.Clear 
     
     
    wSheet.Activate 
    Application.ScreenUpdating = False 
     'Cells.Select
     'Selection.Locked = True
     'Selection.FormulaHidden = True
    Range("A1").Select 
    wSheet.Protect Password:=pword3 
     
     
    Worm:  Next wSheet 
    Application.ScreenUpdating = True 
    Sheets(1).Select 
    On Error Resume Next 
    ActiveWorkbook.Protect Password:=pword3 
    If Err.Number  0 Then MsgBox ActiveWorkbook.name & vbCrLf & "Workbook Failed to Lock!", vbInformation 
     
    Err.Clear 
    On Error Goto 0 
     
     
End Sub 

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


Hi all,
I am quite new to VBA and I am trying to create a macro that does the following:

1. WorkSheet "Email" contains 3 columns: 1st: Full name; 2nd: email; 3rd: choice (yes or no)

2. There are other worksheets and on each worksheet, there is a "Send email" button. Whenever people press this button, it will send an email to people on the "Email" with choice = 'yes'. And this active worksheet will then become the body (in HTML format) of the email.

I got the following code from this forum:


	VB:
	
 Message() 
     
     ' This example use late binding, you don't have to set a reference
     
     ' You must be online when you run the sub
     
    Dim iMsg As Object 
    Dim iConf As Object 
    Dim cell As Range 
     '    Dim Flds As Variant
    Dim lname As String 
    Dim Msg As String 
    Dim Response 
    Dim Answer 
    Dim Question As String 
     
    Application.ScreenUpdating = False 
     
    Set iConf = CreateObject("CDO.Configuration") 
    iConf.Load -1 ' CDO Source Defaults
    Set Flds = iConf.Fields 
    With Flds 
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "LEED" 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
        .Update 
    End With 
     
    lname = ActiveWorkbook.ActiveSheet.name 
    Msg = "Are you sure you want to send an email?" 
    Response = MsgBox(Msg, vbYesNo) 
     
    If Response = vbYes Then 
        Question = "***** Please specify what you have changed *****" 
        Answer = InputBox(Question) 
        For Each cell In Sheets("EmailList").Columns("B").Cells.SpecialCells(xlCellTypeConstants) 
            If cell.Value Like "*@*" And LCase(cell.Offset(0, 1).Value) = "yes" Then 
                Set iMsg = CreateObject("CDO.Message") 
                With iMsg 
                    Set .Configuration = iConf 
                    .To = cell.Value 
                    .From = Environ("USERNAME") & "@abc.ca" 
                    .Subject = "File Updated" 
                    .HTMLBody = "The """ & lname & """ Worksheet has been updated." _ 
                    & "
" _ & "
" _ & "Changes: " & Answer _ & "
" _ & "
" _ & "Library Link" _ & "
" _ & "
" _ & SheetToHTML(ActiveSheet) .Send End With Set iMsg = Nothing End If Next cell Set iConf = Nothing Else Exit Sub End If Application.ScreenUpdating = True End Sub Public Function SheetToHTML(sh As Worksheet) 'Function from Dick Kusleika his site 'http://www.dicks-clicks.com/excel/sheettohtml.htm 'Changed by Ron de Bruin 04-Nov-2003 Dim TempFile As String Dim Nwb As Workbook Dim myshape As Shape Dim fso As Object Dim ts As Object sh.Copy Set Nwb = ActiveWorkbook For Each myshape In Nwb.Sheets(1).Shapes myshape.Delete Next TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" Nwb.SaveAs TempFile, xlHtml Nwb.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) SheetToHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Set Nwb = Nothing Kill TempFile End Function
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

However, sometimes, it doesn't work especially after people using Office 2003 save the file, it will be corrupted. When the next person opens the file and try to send an email, it complains the Fld and then LCase, and even complaint the "ENVIRON". The error is "Compile Error - Can't find project or library"

And the weird thing is that even if I just modified one line, I got the same error.

Any ideas?

Thanks
Ding

I'm afraid the only way I know how to get the worksheet name of an active worksheet using a formula would be using a combination of LEFT and RIGHT statements within a CELL("filename",A1) sort of formula. There has GOT to be an easier way. I know that I can also do something in VBA like:

Private Sub Worksheet_Activate()
[A1].Value = ActiveSheet.Name
End Sub

I'm looking for an alternative to VB and the long formula I am considering with the CELL("filename", A1) option. Is there an easier formula?

_________________
****************** EXCEL/VB NEWBIES ARE MY FAVORITE! ******************
The only dumb question is the one that isn't asked. Pass on what you have learned.

[ This Message was edited by: phantom1975 on 2003-01-02 20:41 ]

I have been able to find some code online for emailing the active worksheet in excel. Is there a way I can email a sheet that is not active instead calling it by name?

(I also need a place for recipients, subject and message body.)

Thanks,

Bill

Hi All, this is my first post so please bear with me.
I'm developing an Excel VBA based system that automatically populates a bunch of word docs via bookmarks from data in one of the worksheets.
Some of the data in the worksheet is name/address/fax.
When the user is entering data to a name field in the worksheet, I need to be able to offer up a list of names from the Outlook Contacts folder from which the user can select a name. Subsequently I want to pull in the other fields (address & fax) for that name, from Outlook contacts.
I hope all that is clear.

I can automate the importing of all the names and other data from Outlook easily enough, into another worksheet in the workbook, but I don't really want to do that as the volume could be huge, and also it would have to be done every time the workbook is opened. I may have no choice, but if anyone knows how to display in Excel, an Outlook names list similar to what one gets when sending an email in Outlook, please tell me about it.
Cheers, Roger

I have VBA code to save an active worksheet as HTML, but I am lost when I try to email the HTML page from Excel.

I need to automatically send the HTML email to a customer using a confirmation number on the workseheet as well as the email addressee.

When I try to email an active worksheet to a customer too much detail is sent & some customers will not be able to read the excel page.

The following code represents only the saving of the HTML page:

****************
Private Sub save_web_page_Click()
Dim Thisfile As String
On Error GoTo quit

Sheets("confirmation_letter").Activate

Thisfile = Worksheets("confirmation_letter").Range("Reservation_Name").Value

ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="C:Documents and SettingsGregMy DocumentsReservationsReservation_web_pages" & Thisfile, _
Sheet:="confirmation_letter", Source:="Print_Area", HtmlType:=xlHtmlStatic).Publish

GoTo Endall
quit:
MsgBox "...WARNING... " & Thisfile & " HTML web page File not Saved"
Endall:
End Sub
***************************

Any help will be greatly appreciated.

Greg

Hi,
I have written a VBA code that queries SQL Server & another Excel sheet using ADO; and then compare both the data to ensure if both are identical. Everything (including querying DB & Excel; comparing the data) works fine, except the following case.

As a limitation, MS Excel allows a worksheet to have a name of maximum length 31. So, when I execute the following query, it goes fine.
Select * From [Portfolio Evaluation Worksheet$C7:C267]
where, 'Portfolio Evaluation Worksheet' is the worksheet name which is of length 30.

The same query fails, when the Worksheet name reaches its maximum length i.e., 31; say the following query
Select * From [Portfolio Evaluation WorksheetA$C7:C267]
where, 'Portfolio Evaluation WorksheetA' is the worksheet name and is of length 31.

Why this is happening so?

As far I can think, it is the '$' which is creating the problem. The Excel which can allow a maximum worksheet name length of 31, is considering the range selection string '$' as well, which makes the total length to 32, & hence Excel is unable to recognize!

Am I right? Is there a solution for this?

To reiterate, this happens only when the Worksheet name is of maximum length 31and doesn't happens when the length is 30.

As of now, I am trying to limit the length to 30, but it is a painful task to keep the name length to 30.

Thanks
Saravanan

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.

Re : Excel VBA Read and Write between the WorkSheet and an Array()

1. Enter an Excel spreadsheet and Insert > Define > Name, RangeA,
pertaining to A1:A100.

2. RangeA is a filled range but interspersed with blank cells in order
to segregate the contents thereof.

3. By using VBA, RangeA is copied to RangeB (same as Insert > Define >
Name, in the range of B1:B100) and sorted thereof.

4. RangeB has become a contiguous range (albeit several blank cells
trail in the lower region of the range).

5. And then, RangeB is read into an Array().

6. Last but not least, Array() is fed back to RangeB in a pattern
that's commensurate with that of RangeA.

7.Meaning that, where RangeA is filled, the corresponding cells in
RangeB will be filled with the contents of Array() ; where RangeA is
interspersed with a blank cell, so there will one alike in RangeB.

8. However, the process of Step 7 consumes considerable time. Is it due
to the requirement of writing to worksheet being much time-consuming ?

9. You see, Steps 3 and 5 are fairly fast. And there, the reads and
writes are presumably more intensive than the corresponding process of
Step 7.

10. Practical experience (in this particular case, for the purpose of
delineation) shows that Steps 3 and 5 would take less than a second
(combined) while Step 7 alone could be 30 seconds or more.

11. What could be done now to speed up the process of Step 7 ?

12. Please share your experience. Regards.

Hi VBA Guru's

I've been looking all day for Copy Active worksheet to all workbooks in folder. I need to copy my sheet "Data Overview" to all workbooks in C:/Asia, I'm using excel 2007 I tried this code below but it says it can't open the file error 1004

 
Sub UpdateAllWorkbooks()

  Dim MyFolder As String
  Dim SrcWks As Worksheet
  Dim Wkb As Workbook
  Dim WkbName As String
  
    Set SrcWks = ThisWorkbook.Worksheets("Data Overview")
    MyFolder = "C:Asia"
    
      WkbName = Dir(MyFolder & "*.xlsx")
        Do While WkbName <> ""
          Set Wkb = Workbooks.Open(WkbName)
            SrcWks.Copy Before:=Wkb.Worksheets("Sheet1")
            Wkb.Close SaveChanges:=True
          WkbName = Dir()
        Loop
        
End Sub
Thanks in advance

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

I've already post this in another forum but the reply I get isn't reallt what I needed and no one is replying me anymore. So can anyone help me out here. This is the forum that I've posted on: http://www.excelforum.com/excel-prog...excel-vba.html

Excel VBA - I have this worksheet that have contestant name, gender and racing time. so for eg I have:

Name | Gender | Time | Overall | MaleRank | FemaleRank
Mitch | Male | 15.06
Jess | Female | 16.06
Rach | Female | 17.07
Will | Male | 17.09
and so on...

I want the system to be able to identify the overall(Both male and female) 1st, 2nd and 3rd places under the Overall column, I also want it to be able to identify the Female 1st, 2nd and 3rd places and identify them under FemaleRank column. So same for Male1st, 2nd and 3rd and identify them under MaleRank. All of the ranks need to be calculate with a button click in excel vba forms. So after I clicked the button, the above example will display:

Name | Gender | Time | Overall | MaleRank | FemaleRank
Mitch | Male__ | 15.06 | 1st___| 1st Male _|
Jessy | Female |16.06 | 2nd___|_________| 1st Female
Rache | Female |17.07|_______|_________| 2nd Female
Wille | Male__ | 17.09 | 3rd____| 2nd Male |
and so on...

p.s the time will first be sorted in ascending order and pls ignore the __'s as they are use to make the columns look more understandable. Actually any kind of way to identify the 1st, 2nd and 3rd places is fine but this is the only way I can think of(by making 3 new columns)...
What I want is vba code to loop through the results and identify the ranks for overall, male and female(only top 3 places). Can anyone please help and give a hint cause I really don't know how to do it.


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