+ Reply to Thread
Results 1 to 8 of 8

Copying value entered in column into another sheet

  1. #1
    Registered User
    Join Date
    10-02-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    4

    Copying value entered in column into another sheet

    Hello,

    I have a general question involving 2 worksheets. Basically one has a column that data is constantly being entered into, thus increasing the # of rows in that column. What I would like to learn to do is to copy the second to last value entered in that column and paste it into another worksheet (doesn't matter exactly where right now as long as in another sheet). I would like it to update the 2nd sheet each time I enter another value in the 1st sheet column.

    For example, in sheet 1, column A, there are 50 rows with numbers in them. I want to take the number from row 49 and paste it into any place in the 2nd sheet. After I enter the 51st value in that column, it should then paste the number from row 50 into the 2nd sheet.

    I know that is a beginner's question but I would really appreciate any specific links or anything else I could read to learn this.

    Thanks

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copying value entered in column into another sheet

    I am not able to clearly understand your question. Maybe you can attach a workbook and even explain the reason behind copying the last cells.

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Copying value entered in column into another sheet

    Hello,

    this can be done with a formula or with macros, depending on your preference. A formula solution might look like this

    =INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A)-1)

    The data is entered in column A in sheet 1 and there are no blank rows (if blank rows are possible, this can be catered for, please advise).

    A VBA solution might look along these lines (put this code into the Sheet module of Sheet1)
    Please Login or Register  to view this content.
    See attached for both solutions at work in Sheet2

    cheers,
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-02-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Copying value entered in column into another sheet

    Thank you very much teylyn. That works perfect.

    Could the same be done while sending that same value to another book like book2?

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Copying value entered in column into another sheet

    Yes. You may want to detail which of the approaches you prefer.

  6. #6
    Registered User
    Join Date
    10-02-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Copying value entered in column into another sheet

    I like the vba but whichever is fine.

    Once again thank you for your replies.

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Copying value entered in column into another sheet

    With both files open, try

    Please Login or Register  to view this content.
    Change the names of the files to your respective workbook names.
    Last edited by teylyn; 10-03-2011 at 07:32 PM.

  8. #8
    Registered User
    Join Date
    10-02-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Copying value entered in column into another sheet

    Where does that code go? In sheet1 of book1? Does it specify in what cell of book2 it pastes the data into?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1