1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: updating cell in active row on another sheet

Discussion in 'Business Applications' started by spooky1, Nov 7, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. spooky1

    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?
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  3. spooky1

    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.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  5. spooky1

    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)
     
  6. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1025913

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice