Index Of Active Worksheet

Aug 28, 2007

I've been a somewhat casual Excel user and now need to do some VBA with a workbook that has several worksheets in it. In particular, the workbook has sheets that have been added "out of order", that is, the leftmost sheet is not the first one added to the book.

I need to write a VBA routine that looks "backward" from the current worksheet to "previous" sheets, i.e., sheets to the left of the current worksheet, but not to the right, so I can't refer to them as Sheet1, Sheet2, etc. and they actual display names that are not of that form anyway. I am aware of the "Worksheets" collection and the fact that I can "subscript" that to refer to the sheets in a left-to-right form.

I know that I can refer to the current sheet as "ActiveSheet" and I can determine properties like its name with "ActiveSheet.Name", but is it possible to find its index in the Worksheets collection? I'm looking for a function or property such that "ActiveSheet.Index" will allow me to refer to it as "Worksheets(Index)" and particularly to allow me to refer to preceding sheets as, e.g., "Worksheets(Index-1)".

If worse comes to worst, I can always cycle through the worksheets and check the name of each worksheet like this:


Sub Button1_Click()
Dim index As Integer
Dim strASName As String
strASName = ActiveSheet.Name
index = 0
Do
index = index + 1
Loop Until Worksheets(index).Name = strASName
MsgBox "Index of current worksheet is " & index
End Sub

And while I'm at it, is it possible in Excel to rename a control like "Button1" to something more sensible and mnemonic, like "btnRecalculate" or whatever?

View 9 Replies


ADVERTISEMENT

VBA Grab Active Window Index

Jul 4, 2007

Dim iStartBook As Long, iNewBook As Long
iStartBook = ActiveWindow.Index
ActiveWindow.NewWindow
iNewBook = ActiveWindow.Index
I'm trying to grab the "2" that .NewWindow generates. The above gives 1 both times, despite the obvious 2 in the caption after you run it. So what property works? What does .Index mean here?

View 9 Replies View Related

Set Textbox Control Source To Hidden Worksheet NOT Active Worksheet?

May 6, 2014

I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.

I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.

The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.

how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?

View 3 Replies View Related

INDEX(MATCH Using Value From One Worksheet To Locate Data In Another Worksheet

Feb 13, 2009

Here is the situation:

On Sheet1:

A
123
456
789

On Sheet2:

D, G
aaa123, 11
bbb456, 22
ccc789, 33

I would like the function to search strings within an array on Sheet2 for a value on Sheet1 (that is, to search for 123 within the strings aaa123, bbb456, ccc789,etc.). Once a match is found, I would like the function to return another value from the same row on Sheet2 that contains the value from Sheet1 (within the string).

Can you help me?

I must use cell references for the values on Sheet1 because I am working with thousands of unique supplier numbers. In addition, all cell formats are general. I prefer to use the INDEX(MATCH combination rather than the VLOOKUP option.

View 8 Replies View Related

Specifying Active Worksheet In VBA

Feb 16, 2009

I used the "record macro" and went through my data manipulation steps. But its not transferable to other worksheets because it imbedding the name of the worksheet in the some functions (sort, pivottable,...). How can I change the name of the worksheet to "activeworksheet"?

View 2 Replies View Related

Copy The Active Worksheet

Jun 30, 2009

I have this macro created with macro recorder that makes a copy of my worksheet.

View 2 Replies View Related

Getting A Count Value From A Non-active Worksheet.

Dec 8, 2009

I am attempting to use the following line:

View 4 Replies View Related

Rename Active Worksheet

Aug 25, 2006

I'm looking for a macro to automatically rename the active worksheet to Sheet1.

View 3 Replies View Related

AutoFilter Non Active Worksheet

Oct 2, 2007

I have a chart that gets updated to show the desired reporting period by clicking a button to run some code which applies filtering to the source worksheet using a value selected from a drop down validation list on the chart worksheet. When the code runs, the source worksheet displays temporarily. How do I prevent this switching back and forth between the data and chart worksheets during code execution? Undoubtedly, there is a better way to code this.

Sub Chart_FilterPPM()
Application.DisplayAlerts = False
wk = Worksheets("Charts"). Range("D63")
Worksheets("Leak Data").Activate
With Worksheets("Leak Data")
.AutoFilterMode = False
.Range("Headings_LeakData").AutoFilter
End With
With Range("Headings_LeakData").AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=wk
Selection.AutoFilter Field:=11, Criteria1:=">5000", Operator:=xlAnd
End With
Worksheets("Charts").Activate
Application.DisplayAlerts = False
End Sub

View 3 Replies View Related

Separate And Save Active Worksheet?

Feb 26, 2014

Using VBA, I am trying (without success) to copy the active worksheet of my workbook and save it in the current folder using a filename shown in cell A1. I only need to save values and formats. Any existing code (auto fit) contained withing the sheet would no longer be required. I get a VB project message relating to macros. I would anticipate saving as xlsx would deal with this but again, am at a loss.

View 4 Replies View Related

Accessing Name Property Of Active Worksheet

Feb 23, 2012

In VB when I select a worksheet, I can amend the (Name) of the worksheet in the properties window. There is also the .Name property which is the same as the sheet tag name.

I can use the (Name) in vb code to identify the sheet e.g.

ControlSheet.cells(1,1)

without having to define ControlSheet as a worksheet first.

How do I access the (Name) which appears at the top of the list of properties in the properties window.

View 1 Replies View Related

Application Error When Worksheet Not Active

Mar 12, 2014

I am having an error in the following line of my code:

Set MyRange = Sheets("BackData").Range("rsJobTypes").Range(Cells(2, 1), Cells(cnt, 1))

If there is another sheet that is active, besides for the "BackData" sheet, I get an application error. I would like to know how I can reference this range without having to activate the sheet.

I tried adding "thisworkbook" before "sheets", but it did not seem to work.

View 3 Replies View Related

Macro To Change Name Of Active Worksheet?

Mar 27, 2014

Say i have a Worksheet named "gateway" or sometimes it will be "gateway (2)" (3) and so on. Is there a macro that i can call that in some ways calls the active worksheet and renames it to just "gateway" everytime?

View 2 Replies View Related

Copying Data From Active Worksheet Using VBA?

Apr 30, 2014

I have a code that I intend to use to retrieve 2 ranges from an active workbook (csv) and place those ranges in another named workbook. Both files are open during this procedure. The code I have is:

Code:
Dim rngA As Range
Dim rngB As Range
With ActiveWorkbook.ActiveSheet

[Code].....

The problem is nothing is displaying in Workbooks("data recorder template UTD Nodata").Sheets(".") I think that the code is not picking up the active workbook correctly but not sure.

View 2 Replies View Related

Find Active Cell On Another Worksheet & Go To It

Sep 28, 2009

I would like to be able to Click a Cell or Button to enable me to go from the 'VIN ENTRY' worksheet to the 'Date Completed' worksheet and then be able to enter the Date in the Correct cell cooresponding to the last 6 digits of the VIN. I have included the File

WorkSheet 'Date Completed

ENTER Last 6 Digits of VIN:K43070Click

Here to Enter Date Completed

WorkSheet 'Date Completed

Colum A Column B...................

View 3 Replies View Related

Making Worksheet Active With Macro

May 12, 2006

Basically trying to make change the active worksheet using a macro code. I want the user to be able to click a button and it will automatically switch the worksheet to another within the workbook.

View 5 Replies View Related

Replace Text On Non Active Worksheet

Aug 29, 2007

Replace specific text within a defined range without having to select the sheet. I tried the following but this is obviously not the way to go.

Sub Open_Calls_Rename_Organizations()
With Sheets("Open Calls").Range("Organizations")
Cells.Replace What:="Institute Technology Code", Replacement:="ITC", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With
End Sub

View 3 Replies View Related

Check Cell On Non Active Worksheet

Dec 6, 2007

Trying to reference cells in other worksheet. Error comes after top line of code.

If Worksheets("Cheat Sheet").Cells(intActiveRow, intActiveCol) = "Mine" Then
Cells(intActiveRow, intActiveCol).Interior.Color = vbRed
MsgBox "You Lose!", vbOKOnly, "MineSweeper"
UserForm1.Enabled = True
cmdUncover.Enabled = False
cmdMark.Enabled = False
Exit Sub
End If

View 2 Replies View Related

Rename Active Worksheet From Personal.xls Macro

Mar 12, 2009

I am having trouble renaming an active sheet from a Macro I stored in the personal.xls file. I want the active sheet to be renamed to "Data_Source" and then the rest of the code can kick in. Instead of renaming the current worksheet it creates a new one.

View 4 Replies View Related

Current Cell Position For A Non Active Worksheet

Dec 15, 2009

I need the cell address where the cursor is (in a non active worksheet).

View 5 Replies View Related

Macro To Filter Table Using Active Worksheet Name?

Jun 7, 2013

I need the macro to filter a table using the name of active worksheet as criteria. The code that I am writing is as below, but it doesn't seem work:

ActiveSheet.Range("$A$1:$AE$421").AutoFilter Field:=19, Criteria1:="Activesheet.Name"

If I hard quote the name of the sheet then, the macro, unchecks all the criteria in the filter and does not show and row in the table.

View 1 Replies View Related

Reduce Number Of Active Rows In A Worksheet

May 13, 2009

i have data in 1 worksheet say sheet1 which i copy to another sheet say sheet2. i do some processing like using some formulas on the data from columns A to D and getting an output in column E in the same sheet. Now the problem is, before i start the calculations in sheet2, the file size is 400 kB... and after the calculations are done, it becomes 20 mB... i dont know why this happens... there are some things which i think might be a reason but not very sure...

1. i calculate the last row in column A using
lastrow = Range("A65536").End(xlUp).row

i tried Range("A500") and it seemed fine... Now u may tell me that i can just use 500 and make things simpler but its just a workaround and not a solution...

2. i use macros.. this is just an info...

3. Another thing is, if i use 500, the number of active rows is 500... i dont know whether the right word is active rows but wat im trying to say is, the scroll bar for the rows movement reaches row 500 when it reaches the bottom.

View 9 Replies View Related

Data Active Worksheet Shall Be Automatically Transferred To Ms

Dec 1, 2009

i have a set of data in excel. i want that all the data in excel active worksheet shall be automatically transferred to ms word when i click a command button...

View 9 Replies View Related

Macro Code To Change Active Worksheet

May 12, 2006

I have a workbook with about 12 worksheets within it. I am trying to input a button on worksheet 3 that when pushed would activate worksheet 12. Basically the same as if you clicked on the tab at the bottom of the screen called worksheet 12.

I realise that you have to create a button which Ive done. However I cannot seem to figure out the proper macro code to get the button to change the current worksheet when its pushed.

View 4 Replies View Related

Command Button Exist In Active Worksheet Or Not

Aug 29, 2007

I am using following command to check if a command button with 'Email This Page' written on it Exists in active worksheet or not. It always gives not present.

Dim s As String
On Error Resume Next
s = Application. CommandBars(1).Controls("Email This Page").Caption
If Err.Number = 0 Then
MsgBox "It exists"
Else
MsgBox "Not here"
End If

View 2 Replies View Related

Create Worksheet Index

Aug 16, 2006

Can an index of all worksheets names be created without using VBA?

View 3 Replies View Related

Excel 2010 :: Macros - How To Update Active Worksheet Only

Oct 7, 2011

I am using excel 2010.

I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.

However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...

How can I update each sheet individually, without compromising the other sheets?

Excel is not responding to Shift - F9.

View 3 Replies View Related

Macro To Create Pivot Table On Active Worksheet?

Jun 20, 2012

I have recorded a macro to create a pivot table. I thought I had it so that it would create the pivot from the active worksheet only. But looking at the code, it is picking up the sheet name from the one I recorded it from

Code:
Sub SalPiv()
'
' SalPiv Macro
' Macro recorded 20/06/2012 by imccormick

[Code].....

View 1 Replies View Related

Deleting Workbook Based On Criteria In Active Worksheet?

Aug 16, 2012

I am trying to delete a workbook, yes the whole workbook, if cell A1 is blank.

View 2 Replies View Related

Clone Active Worksheet And Prompt User For Where To Save CSV

May 29, 2013

I've recorded this code and am looking to include it in a button. I would like for when a user clicks the button assinged macro that the active sheet is cloned and saved as a CSV file. The user should be prompted before saving on where (file location) they'd like to save the file.

Sub CloneWorksheet()
'
' CloneWorksheet Macro
'
'
Sheets("SDW&Customer Workshop scheduled").Select
Sheets("SDW&Customer Workshop scheduled").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings1167916My DocumentsTestBook1.csv", FileFormat:= _
xlCSV, CreateBackup:=False
End Sub

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved