Solved: updating cell in active row on another sheet

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

spooky1

Thread Starter
Joined
Jan 29, 2010
Messages
126
select row in sheet1, gather data, select sheet2 & paste gathered data.

Problem is how to update a cell in the active row of sheet1 without having to goback and select sheet1
Just update a cell in sheet1 whilst staying in sheet2

I tried
Sheets("newquick").Application.WorksheetFunction.ActiveCell.EntireRow.Cells(1, 71) = 1
but no luck

It would not be the same row in sheet1 all the time.

Can row number be used somehow?
 
Joined
Sep 4, 2003
Messages
4,916
The activecell property is only applicable to the current sheet. There really is no need to leave the first sheet since you can copy data to the second sheet in the background without having to select it first. Can you post a sample workbook showing the data layout. There are probably other ways to achieve what you want but it's hard to offer suggestions without being able to see the structure.


Rollin
 

spooky1

Thread Starter
Joined
Jan 29, 2010
Messages
126
Thank u rollin again for the response. May I try and explain a bit further as to what I need to acheive.

Sheet1, select a row, any row, copy some col data from selected row to public variables> goto sheet2, paste public variables & view pasted data, if all okay update active row on sheet1 as having been processed by adding "done"
into col (eg 72) in active row of sheet1 whilst still in sheet2

(switching between sheets works as the last used row in sheet1 remains active after switching to sheet2 and upon switching back from sheet2 one can update any col in that last used active row of sheet1 by using entire row code)

Obviously the row number on sheet1 can be any row. Any clues, please? If this explanation is not adequate I will have to dummy up some code and post it here. Please let me know if I really need to post an example.

I realize I could actually flag col 72 as "done" before switching to sheet2. But it would be better if I could do it after succcessfully completing the process on sheet2 and viewing it then flagging that the row on sheet1 where the data was harvested from was succesful. Need to do this using vba and by not having to switch back to sheet1, if possible.
 
Joined
Sep 4, 2003
Messages
4,916
So why not set a variable equal to the active row number when the macro is fired. Once you have the active row number set to the variable you can set the cell value on sheet1 without having to select the sheet again first.

Code:
vRow = ActiveCell.Row

Sheets("Sheet2").Select

Sheets("Sheet1").Cells(vRow, 72).Value = "DONE"
Rollin
 

spooky1

Thread Starter
Joined
Jan 29, 2010
Messages
126
Mate! If I was that clever I would be on this site too, giving excel advice!
Simple brilliant solution that would have taken me another 2 years to work out. Thank u so much. I learn so much from u guys. (y)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top