New Lines To Refer To Different Sheets

Nov 20, 2009


A2 refers to a linked cell but I have tried without the link there still no joy. Together they would generate for example "DCA_RES" which would refer to a column range in a different sheet on the same workbook.

I want this to work so that I can implement more easily new lines to refer to different sheets. (Drag to copy) instead of having to copy and edit a formula 100 times.

View 6 Replies


Refer To Sheets Created In Loop

Oct 16, 2007

I need to creat a bunch of sheets and do the same work for each of the sheets. My problem is that I do not know how to refer them by the created name. Here is an example of my codes:

Sub test()
Dim I As Integer

For I = 1998 To 2010
Sheets.Add.Name = I

Next I

End Sub

The error is "Subscript out of Range". I believe it is because when I refer a sheets(I), (I) does not recognize as the name of the sheet but the number of that sheet, and there is no sheet numbered 1998 or bigger. However, If I refer the sheet as sheets("I"), it can not find the sheet named "I" either.

How should I refer those sheets name so that I can do some work?

View 7 Replies View Related

Sheet Indirection. Refer To Sheets With Changing Names

Feb 25, 2008

I've been referencing sheets in my code directly with things like

Sheet1. Range(NamedRange)

however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to

#define SHEET_SC_CONFIG Sheet1
#define SHEET_CUSTOMER Sheet2

'and then use these throughout the code....

(cannot reference by sheet name, as end user may change these)

View 6 Replies View Related

Create Sheets Based On Lines

Mar 25, 2014

result of command is like below:

YYY,24/03/2014,222 , 0002,ALARM TEXT DIFFER IN TIMEFORMAT , 1, 1,3.03030303
YYY,24/03/2014,333 , 0001,SCRIPT FAILED , 31, 6,93.9393939
YYY,24/03/2014,111 , 0003,ALARMS NOT RECEIVED , 1, 1,3.03030303
YYY,9999992867290,24/03/2014 ,333 , 0001,SCRIPT FAILED , 7, 1, 100
YYY,9999992813525,24/03/2014 ,333 , 0001,SCRIPT FAILED , 3, 1, 100


Now I'm looking is when opened in Excel, it should create a two sheets excel file 1) with the First Section and the 2nd with second section . based on headers included in file.which then will be used for 2nd stage of automating..

View 3 Replies View Related

Saving Sheets As Text Files Results In Blank Lines

May 11, 2008

I load an text file into an excel sheet and after some eventually changes I save (overwrite) it back into the text file again. Now the text file has become a file with 50 pages (49 empty pages). How can I avoid this or how can I set an EOF when no more written lines follows. The code for saving the excel sheet is this:

Application. ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=WorkFolder & WorkFile, _
FileFormat:=xlText, CreateBackup:=False

View 6 Replies View Related

VBA To Refer To Outlook

Jun 18, 2009

In Excel, I want VBA to refer to the SENT ITEMS folder in Outlook. I want to loop over all sent items, and if the email address satisfies a condition, then record that fact in Excel. I am good at VBA in Excel, and I have a passing knowledge of VBA in Outlook, but I don't know how to refer to Outlook within my Excel VBA.

View 2 Replies View Related

Refer To Combo Box In VBA

Sep 14, 2009

How do I refer to a combo box (form control) in a calculation. EG: how can i say if comboBox = Something then ....

View 5 Replies View Related

Refer To More Than One Workbook

Nov 9, 2009

I'm using this code to open another workbook:

Dim FileToOpen
FileToOpen = Application.GetOpenFilename _
(Title:="Please locate BlankSAMSRegister.xlsm", _
FileFilter:="BlankSAMSRegister.xlsm (*.xlsm),")
If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Error"
Exit Sub
End If
Once this second workbook is open, how do I refer to each one within the subsequent code?

I really need the parent workbook to be named as wb1 and the child to be names wb2 so I can flick between the two in the next macro... I can get the parent using, but how do I get the child?

View 9 Replies View Related

Refer To A Checkbox Value By Vba

May 10, 2007

I have a check box whithin a worksheet. This CheckBox doesn't have any linked cell, and doesn't have any macro asigned. know I would like to refer to the value of the checkbox by vba kind of: ?ActiveSheet.Shapes("CheckBox1").value=true 'from the vba's inmediate window

View 4 Replies View Related

Use INDIRECT To Refer Sheet Name

May 9, 2014

I have multiple sheets as Name1 ; Name2 ; Name3 and each sheet have a value on cell A1 for any number let say between 0 to 100. On another sheet call SheetName, I have A1=Name1 ; A2=Name2 ; A3=Name3.

I have formula as:

[Code] .....

The INDIRECT formula work to refer each cell A1 in each sheet, however if the sheet name include a space then it won't work anymore. How to make it work with the sheet name with space?

View 5 Replies View Related

Ignore Day And Only Refer To The The Month?

Mar 9, 2009

The summary page summarises the source data into how much revenue each department has made over 12 months.

However the formula in Summary worksheet column B will only total up the amounts if the month in the Source worksheet column B is the 1st of the month. How do I change the formula in Summary worksheet column B to purely go by month and ignore the day?

View 4 Replies View Related

Formulas Refer To Other Tabs

Jul 17, 2009

The "summary" tab will be calculating 20 statistics for each of the 30 or more tabs. In an effort to automate this - can I replace writing "January!" with a cell reference? For example;
instead of writing = sum(january!a:a). Could I write = sum("A1"a:a), with the A1 Cell = january!. Obviously I tried this and it does not work but I am not sure if there are other options.

View 2 Replies View Related

Using INDIRECT For Column Refer Only

Jul 20, 2009

Let's say I have a formula in cell A1 that is =COLUMN(L5). So cell A1 returns the result 12 (for column L).

I now want to create an index formula in another cell:

=INDEX(C1:C12, ........), but I want the 12 in C12 to be picked up from the result of cell A1

So, I tried various things like =INDEX(C1:C&INDIRECT(A1)....etc but I can't work out the correct way of doing this.

View 6 Replies View Related

Refer To Previous Sheet

Nov 24, 2009

I've got a workbook that increments in sheets for each new week. I have a cell showing the current week number for that sheet ( lets say cell C1) and any formulas that need to reference data from the previous sheet do in 'indirect' function which basically looks at the current week number ( lets say week 20)in cell C1 and minus's 1 to navigate to the previous week (week 19).

I was wondering if there was a way to reference the previous sheet purely by the order they're in.

So say i have 5 sheets named "1,4,5,8,9" and these represent week numbers so the sheet named '8' in cell C1 would have '8' standanding for the week number but cells that wanted to reference the previous sheet couldn't do the 'indirect' C1-1 as there is no 'week 7'.

The weeks used could vary alot so a formula to reference the directly previous sheet is needed.

ok, thought best if i show you the actual formula


the '$A$G2-1' part is the bit that redirects to the previous sheet based on it's name, what i think i really need is to replace this part with a 'PrevSheet' function but i'm unsure how.

View 11 Replies View Related

How To Refer To Object Which Is In Another Subroutine

Apr 3, 2014

Is it possible to refer to an object which is in another subroutine?

Or should i have to give its definition in each sub i need it?

View 7 Replies View Related

Refer To The Last Row In A Selected Area Using VBA..?

Nov 3, 2008

I'm trying to do is add a signature box (for inventory-taking accountability) directly below the pasted area after I paste it. Now, of course, the copied and pasted area could have 5 rows or it could have 15, and when I copy it from the first sheet and paste it into the second it doesn't preserve the name of the range on the second page, so what I really need to do to add the signature box directly below the last row in the pasted area is specify the location in relation to the last row in the pasted selection, but I don't know how to do that. Using ActiveCell always refers to the top-left-most cell in the selected area. So...

If I have an area selected, how do I refer to the bottom-right-most cell in that selection (in the same way that ActiveCell refers to the top-left-most cell)? Or at least the bottom row? Or, even better, the row below the bottom row?

View 2 Replies View Related

Indirectly Refer To Function

Oct 26, 2009

I'm trying to call a function that is stored in a cell as a text string. In cell A1, I have the text Sum('Sheet1:Sheet3'!C3). I want to call this function from a different cell. Is this possible using the pre-defined functions, or would I be better creating my own function?

View 4 Replies View Related

Refer To Cell Without Offset

Aug 31, 2012

I have a selection that I am going through with a 'for each' and then performing actions if the cells meet a certain criteria. I have been referencing nearby cells with the offset function, but now my sheet is too big and I need to change the code often and the offset function is difficult, is there anyway to refer to cells without the offset function, preferably by column letter

PHP Code:

For Each Cel in SelectionIf Cel = x Thenvalue1 = Cel.Offset(0,39).Valuevalue2 = Cel.Offset(0,5).ValueEnd IfNext Cel 

View 9 Replies View Related

VBA Add Define Name To Refer To Last Row And Last Columns

Nov 7, 2013

with the below code i want the highlighted in red (RC11) to refer to last column and last row.

Sub try ()
Dim finalrow As Long
Dim finalcol As Long
Set wspo = Worksheets("Previous Order")
finalrow = wspo.Cells(Rows.Count, 1).End(xlUp).Row
finalcol = wspo.Cells(1, Columns.Count).End(xlToLeft).Column


View 2 Replies View Related

Refer To Sheet By Secret Name

Jun 9, 2006

When I look in VBA under the Microsoft Excel Objects at the sheet names in my workbook I see two names for the same sheet like so:

sOrdersFB(Orders From Bob)

how to select using the first name sOrdersFB?

View 9 Replies View Related

Refer To Cells That Move

Dec 7, 2006

In my current spreadsheet I use Offset to change the values of various cells. The problem is that if I make any changes to the columns it throws the whole offset thing off. So, instead of using offset to paste all the data is there an alternative that would work better?

I was thinking maybe I could name a column as a range and then have the cell in that row equal a value.

What solutions are there?

ActiveCell = TextBox1.Value
ActiveCell.Offset(0, 1) = TextBox2.Value
ActiveCell.Offset(0, 2) = ComboBox9.Value
ActiveCell.Offset(0, 3) = ComboBox2.Value
ActiveCell.Offset(0, 4) = ComboBox3.Value
ActiveCell.Offset(0, 5) = ComboBox11.Value

ActiveCell.Offset(0, 12) = ComboBox1.Value
ActiveCell.Offset(0, 13) = TextBox6.Value / 100
ActiveCell.Offset(0, 16) = TextBox4.Value
ActiveCell.Offset(0, 17) = TextBox5.Value
ActiveCell.Offset(0, 18) = CostBasis
ActiveCell.Offset(0, 19) = NumberOfShares
ActiveCell.Offset(0, 59) = DividendFreq

View 9 Replies View Related

Refer To Objects In Other Forms

Mar 13, 2007

I'm trying to refer to a togglebutton in a form from a command button in a different form.

Right now I have

Private Sub CommandButton1_Click()

If ToggleButton1.Value = True Then
End If

End Sub

but I assume I have to preceed the Togglebutton1 with the name of the form that it is in but I am not sure how.

I don't even know if the above code will work as obviously I haven't got that far may be completely off...

View 3 Replies View Related

Refer To Named Constant In VBA

Oct 1, 2007

I created a name in Excel 2002, Name = "NameToUse", Refers to = "Bill".
How do I get the value in VBA code?

Range("NameToUse").value ' doesnt work
NameToUse ' doesn't work

View 3 Replies View Related

Sum On Multiple Tabs + Refer To Specific Tab

Dec 13, 2009

I have a workschedule in excel.

It has a tab for every week in a year so 52 in total. The first tab is supposed to give some general information. I have a few questions on how to implement things.

1 i wanna be able to view the Total amount hours worked in my current week.

I calculate the current workweek using:

View 6 Replies View Related

Conditional Formatting - Refer To Another Cell

Dec 31, 2013

In conditional formatting, which formula do I use to refer to another cell? Trying


without luck.

View 9 Replies View Related

Refer To Each Particular Item In Listbox For A Range

Jul 15, 2014

I have a userform with a ListBox and I'm trying to refer to each particular item in the Listbox for a range but it doesn't work.

[Code] .....

View 14 Replies View Related

Refer To A Sheet's Codename From Another Workbook?

Apr 17, 2009

I want to be able to safeguard against a user changing the name of a worksheet, so I use the sheet's codename in all my macros.

I have the codename of a sheet: shOptions

From a different workbook, "ActiveWorkbook.shOptions" doesn't work. How can I use "shOptions" in the other workbook.

View 14 Replies View Related

2000: How To Refer To Worksheet In The Same Book

Nov 12, 2005

I have twelve sheets in the book called "Jan", "Feb" to "Dec". Now I will use value in the two sheets by this format:

Row1 will contain columns, "jan", "feb", and etc.

Row2 should be like "=JAN!b2", "=FEB!b2", etc till "=Dec!b2".

Now the question is, how can I use reference, but not directly put in the sheetname such as "Jan!b2"?

In another word, what I want to use is like "=Worksheets(cells(...))!b2".

View 11 Replies View Related

Using Text In A Cell To Refer To Worksheet Name

Jun 6, 2003

How can I use (with or without macro) cell text to refer to different worksheets inside a formula. For example I've formula =INDEX(Mary!B9:E17...) but I wanted to write that 'Mary' or whatever sheet name to a cell in one sheet and sheet refenrence in this index-formula would change accordingly.

View 5 Replies View Related

Refer To Workbook With Name That Is Created By Macro?

Sep 28, 2011

I have the following code that creates a new workbook and names it after the value of a cell in another workbook. Another part of the macro will switch back and forth between the original workbook and the newly created workbook.

What is the best way to refer to new workbook? I'm familiar with the "workbooks "VARIABLE NAME".Activate" style but, the name of the new workbook will change every time the macro runs and I'm not sure how to refer to it.

the code below.

HTML Code:
Sub MakeNewBook()
Dim wB As Workbook
Dim nPath As String


View 3 Replies View Related

Copyrights 2005-15, All rights reserved