Finding the currently selected cell in another worksheet

Hi,

In Excel 2002 I want to display the current row of worksheet A in worksheet
B (in another format).

Does anyone know how to do this? Ideal would be if this updates
automatically, but performance wise I guess a macro activated by a button in
worksheet A would be the best solution.

TBH, Jeroen


0
jpmm (1)
7/6/2005 8:41:40 PM
excel 39879 articles. 2 followers. Follow

1 Replies
283 Views

Similar Articles

[PageSpeed] 59

Jeroen,

I don't know of a way in a formula to determine the active cell of the 
worksheet.  Worksheets other than the active one don't have an active cell. 
I think macros are the only way.  Put this in a regular module:

Public Roww As Long

Put this in the sheet A module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Roww = ActiveCell.Row ' get current row
End Sub

Put this in the sheet B module:

Private Sub Worksheet_Activate()
' put column 5 of active cell row sheet A into this sheet A2:
Range("A2") = Sheets("A").Cells(Roww, 5)
' put column 2 of active cell row sheet A into this sheet B2:
Range("B2") = Sheets("A").Cells(Roww, 2)
End Sub

Now when you select a cell in Sheet A, it the row number will be stored in 
public variable Roww.  When you select Sheet B, it will populate the cells 
with cells from the row of the active cell of sheet A.  No buttons needed.
-- 
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Jeroen Moolhuijsen" <jpmm@dds.nl> wrote in message 
news:42cc4202$0$16447$ba620dc5@text.nova.planet.nl...
> Hi,
>
> In Excel 2002 I want to display the current row of worksheet A in 
> worksheet
> B (in another format).
>
> Does anyone know how to do this? Ideal would be if this updates
> automatically, but performance wise I guess a macro activated by a button 
> in
> worksheet A would be the best solution.
>
> TBH, Jeroen
>
> 


0
nothanks4548 (968)
7/7/2005 12:39:12 AM
Reply:

Similar Artilces:

Can't Select Addresses for "Send Email Statements" Function
When I try to select an email address from my Outlook address book on the "send email statements" area on the customer maintenance options window, the email does not display. I get the dialog box to select the email from my address book, but after clicking ok, the address does not display in the appropriate field (I've tried TO:, CC:, and BCC:). I'm using Windows 7 pro 64 bit and Outlook 2007. Any ideas? More info on this. Other users who are running Windows XP and Outlook 2003 can select entries from their address book and have them show up in the appropr...

Macro syntax to open file in current explorer folder
Pt 1 : MS Excel 97 SR-2 ----------------------- I would like to use a macro code line starting Workbooks.OpenText FileName:=".\AmsAll-1.prn", _ to open the tab delimited file "AmsAll-1.prn" in the folder from which I launch the Excel application. However the ".\" path qualifier always reverts to the C: drive, not the K: mapped drive that I access all my project files from. It is inconvenient to navigate several levels down to the project from C:\ just to run this one application. The following does work : Workbooks.OpenText FileName:="k:.\AmsAll-1...

The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address.
Please excuse the rookie question, I have been avoiding Exchange too long and now it has bitten me in the... elbow... I am attempting to set up Exchange to send mail... Whenever I send to a member of my domain (hosted externally) I get this error message... (I am using SBS, I have posted a similar message over there.) The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address. It appears that email sent to other domains is delivered without incident. I need to get the squar...

Can you filter or sort by a cell's formatting? #2
Can you filter or sort by the formatting of a cell - these were formatted manually and do not have conditional formatting applied to them - thanks You can even use a UserDefined Function that can return the colorindex. Chip Pearson has a nice function at: http://www.cpearson.com/excel/colors.htm Returning The ColorIndex Of A Cell If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm MalibuinthePeg wrote: > > Can you filter or sort by the formatting of a cell - these were formatted > manually and do...

how to find the stack memory violation??? Urgent
hi pals, While running my win 32 console application, Global variable becomes NULL at some times, which was already allocated. My application got affected by some memory violation it seems or any other reason for getting such violation.. How to find the stack memory violation? I need your help. Forgive me if this is not a relevant group, please give me appropriate news group for this query. @ Shahul. If the variable is global, then there is no stack involved to store it's content. If you have multiple threads that're accessing your variable then you should pay attention to...

OWA current user
hi , i want to know if its possible to know the current user logged to the owa via code . i have a logon page in which i need to update user details after logon so i want thinking to use the owaauth.dll to sign in the user and redirect the other asp page . in the asp page i need to know which user logged on. 10x ...

Linking cells in a worksheet to other worksheets in a workbook
I'd like to be able to link a cell in one worksheet to another worksheet, so that when I click on the cell in the first worksheet, the other worksheet opens up. I have a spreadsheet where the first page (worksheet) has a listing of items contained in other tabs (worksheets). I'd like to be able to click on the first worksheet "index" and have the appropriate worksheet pop open with the contents of the index. Thanks for your responses! (In Excel 2003) Select a cell to which you would like to add a link. Select Insert->Hyperlink. Click the "Place in this doc...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

Do a calculation in cells with text data format
I have a few columns of cells having a mixed data format of number and text. Is it possible to convert the first row of numbers in text data format for further calculation? Your guidance to accomplish it is appreciated. Thanks, Ray Example? -- Regards, Peo Sjoblom "Ray" <NoSpam-ZQLi@GMail.com> wrote in message news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl... > I have a few columns of cells having a mixed data format of number and text. > Is it possible to convert the first row of numbers in text data format for > further calculation? Your guidance to accomplis...

Publisher 2003: How Do I "Flow" (rework) a CURRENT [saved] document?
Page Setup/Layout/Booklet/Landscape/Print two pages per sheet. With both Automatic and Manual "Two Sided Printing" the center page (both sides) is--- blank. At the bottom right, of each page in the document, is a "Text in Overflow" Icon. Right click does not present an option to--- flow, condense or correct. Thanks Johann Since you know the terminology, I'm a bit curious why you didn't look up "overflow" in the Help file. The very first link will tell you how to deal with this. -- JoAnn Paules MVP Microsoft [Publisher] "Johann B�cker&qu...

Changing of Cell protections after saving Excel File (2002)
This problem occurs when I protect a document using a macro 4.0 function: =PROTECT.DOCUMENT(TRUE,,,TRUE,TRUE). When I use the function within a macro4.0 macro, on an original file, everything works fine. The sheet has unlocked cells, and when the sheet is protected, it allows me to access those cells. But if I save the file, or save.as another name, then the fun begins. The enable selection of the sheet( view codes) has gone from 0-xlNoRestrictions to -4142- xlNoSelection. This locks me out of doing anything in the sheet. When I unprotect and then re-protect the sheet using the T...

how do I change cell references automatically in formulas
In Excel 2000, I have data in 80 rows and 10 columns. Each week I add a new row. I have a separate chart for each column with the data range from the first row to the last.. Each week I have to change the data range to reflect the new last row for each chart. Is there someway I can do this automatically? http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "jnw3" <jnw3@discussions.microsoft.com> wrote in message news:8A3551F9-FBC6-4841-95B4-618AB1893190@micros...

Cell can left indent; anything for right side of cell?
Is there a way to leave more space between the end of a word and the right side of the column? I indented the left side of the column by using the indent option set to "1" in the alignment tab of the formatting box. Besides adding an extra column to the end of the row and making it very small in width and extending the border to that extra "spacer" column, is there a way to make this space? My spreadsheet is done and if I have to do this, I'll have tons of work re-writing code. I was going to use Word for the final presentation of the data, but it didn't work ou...

Defining same name for cells in different sheets
Does anyone know the answer to this one? I want to give the same range name to the same cell reference in a series of worksheets. I find I can do this by pre-defining all my range names on a "master" worksheet and making several copies of the sheet (try it, it works!) But let's say I have done this, and entered all my data on the sheets I have created, and suddenly realize I need another range name. I haven't found any way to define a new range name and apply it to the same range on several sheets. This is not the same as a 3-D reference, which I tried. (3- d referenci...

Alternating cell shading colors for every other merged cell
I am trying to get every other merged row to be a certain cell shading color. There is some discussion in the newsgroups about alternating colors for every row, but in my case I had merged cells and the formulas given didn't turn out right on my tables since they were based on the row numbers. Does anyone know how to have alternating cell colors for merged rows? (the merged rows are random sizes) Thanks. Not an answer to your question I'm afraid, but just for info, most people in here steer clear of merged cells like the plague - they tend to cause far more problems than they ever se...

Fill Down based on count in another sheet
I am using the following in Excel to copy a formula: ThisWorkbook.Worksheets("Results 4").Range("3:2000").FillDown I need to only copy the formula for a certain number of rows up to 2000. The number of rows I need is based on the number of rows I have in worksheet "Results Data" in column A. So, if "Results Data" has data in column A2:A16, I will need to copy down the formula for all columns in worksheet "Results 4" to column 17. Sub FillFown() Dim lStop As Long With Worksheets("Results Data") lStop = .Cells(...

Finding data in grouped sheets
When using the 'find' option whilst workbook pages are grouped together I am getting data from the last page first, then the last but one etc. Can anyone tell me whether it is possible to change this so that it finds the data in the first possible page, ie it looks at page 3 before page 4 etc? What I am trying to do is set up a spreadsheet which finds the first 'vacancy' in a childrens nursery. As I have set it up at the moment it is finding the last vacancy first! All help greatly received! Thanks in advance. Susie Vaughan This may not quite fit, but you may find it very us...

Go To an address specfied in a cell
Hello Folks, Does anyone know how I can move the cursor to a cell, the address of which is specified in another cell? Here is the scenario. I enter a list of hours worked in a specfic week on a data entry sheet. I hit a button and the values are copied to a data summary sheet, the position depends on the Week No., the first cell is specfied as the address "Data!29" for Week 5. I reckon I can handle a recorded macro to copy and paste the data but how do I locate the correct start cell? I have tried copying and pasting into the GoTo box but that doesn't work. Data!J29 Wee...

Help!! Way to find users sending email to large amount of receipients.
Could some one tell me if there is a way in Exchange 2003 to find out what users are sending email to a large amount of recipients? I'm having some serious performance issues and I'm sure it's some one sending email to a large amount of recipients. Thanks, Will ...

Help with displaying the contents of the last populate cell.
I have numerous sheets within a book where all cells in column C in all sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you reference both columns P and R hold a monetary value and are formatted as Currency. Is there a way that cell D1 can automatically be populated with the contents of the last cell in column C that has a value in it. E.G. Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200. Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250. Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900. Any h...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Excel 97 Worksheet Protection and cell colour
Hi there, One of our users has setup a worksheet will a small range of cells that are locked (they have formulas in them), he then protects the sheet. He then wants to change the colour of some of the other cells, these cells are not locked, but he cannot change the colour of the cells. Is there an obvious solution? Cheers, Andy Hi AFAIK you can't do this in Excel 97 without first removing the protection -- Regards Frank Kabel Frankfurt, Germany andy wrote: > Hi there, > > One of our users has setup a worksheet will a small range of cells > that are locked (they h...

Lookups across worksheets
I need to consolidate some information, using several worksheets in one file. Here's an idea of what I need to do: WORKSHEET 1 - [/B] _FIRST_|__LAST__|_COMPANYID_|_LOCATIONID_ BILL | SMITH | 1 | 3 JEN | JONES | 2 | 1 [B]WORKSHEET 2 - _Company_ID_|__Name_ 1 | Acme 2 | Widgets 3 | Bucky's WORKSHEET 3 - _LocationID___|__Name_ 1 | New York 2 | Paris 3 | Rome I need to come up with a set of formulas that give me the following result: WORKSHEET 1 - _F...

Item Quantity Discrepancy and Worksheet 401 and 260 .. HQ Adjustme
I keep getting a HQ Adjustment at the store when I run a 260. This is after I have ran a 401 and a 501. When it makes the adjustment at the store it also seems to make that adjustment at HQ which always creates a discrepancy. I am running 1.3.1010 right now. The store right now shows HQ Adjustment and when I run a 260 it makes an another adjustment.. Keeps doing this over and over again. Even if I do a 501 in the middle. Any ideas? Thanks This is a multi-part message in MIME format. ------=_NextPart_000_0066_01C77325.4B0EBDF0 Content-Type: text/plain; charset="UTF-8" C...

Coloring the Desired cells
Hello, I have a work sheet in which i have to look for word "Test" and color the rows below it. There are different words like "Test 1" "Test 2" and each set needs a different color. Can I get some help with the macro for it? eg: Test1 row 1 row 2 Test 2 row 1 row 2 the number of rows in each group is not constanr. Thank you, Harsh Excel will need to know the logic of the rows and colors to be able to determine how many rows to color. You say the number of rows is not constant, but obviously you know how many rows to color. How do you know t...