Solved Copy data entered to next blank row of another sheet

January 15, 2014 at 12:36:29
Specs: Windows 7
I am entering data on a row at cell B3 to 03 from time to time, the sheet name is "Request". I want all the data i've entered there wil be automatically saved to the next sheet that I have named "Tracker". Can you please provide me a VB code to make this be done easier? Thanks

See More: Copy data entered to next blank row of another sheet

Report •


#1
January 15, 2014 at 16:34:26
When you say "automatically" do you mean that you want the data copied without the user taking any action?

If so, would the entry of data in O3 be a good enough trigger to cause the copy to happen? In other words, you would enter data in B3, C3, D3, etc. and as soon as you enter data in O3, B3:O3 would get copied.

Of course, there's always the issue of errors. In the method described above, once the data gets copied, changing a value on the Request sheet would not correct the data on the Tracker sheet. Worse yet, if you made a change to O3, B3:O3 would get copied to the next row.

Have you thought about how you would deal with errors once the data has been copied?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 15, 2014 at 18:16:23
Hi, The data will just be copied from another system. What I only have to do is to paste it at B3 and every cell accross row 3 will be filled out once entered. I haven't realized the errors that I may encounter. Your suggestion would greatly be appreciated.

message edited by AngieNeoayod


Report •

#3
January 15, 2014 at 18:54:01
✔ Best Answer
This might work for you.

This assumes that you are pasting data into Request!B3:O3 all at once.

Right-Click the sheet tab for the Request sheet, choose View Code and paste the following code into the pane that opens.

Let me know how this works out for you.


Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Request!B3:O3
  If Target.Address = "$B$3:$O$3" Then
'If Yes, determine next empty Row on Tracker sheet
    nxtRw = Sheets("Tracker").Range("B" & Rows.Count).End(xlUp).Row + 1
'Copy Request!B3:O3 to Tracker!B3
     Sheets("Request").Range("B3:O3").Copy _
       Destination:=Sheets("Tracker").Range("B" & nxtRw)
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 15, 2014 at 19:10:25
Hi, I don't know why it's not working.

Report •

#5
January 16, 2014 at 09:51:31
I don't know why it's not working either mainly because I can't see your workbook from where I'm sitting.

Here's the process I followed for which it works. Only you can tell me what differs between your process and mine.

1 - I have a sheet named Request and a sheet named Tracker
2 - I right-clicked the sheet tab for Request, chose View Code and pasted the code from my post above into the pane that opened.
3 - In Request!B10:O10 I entered some data so that I would have something to copy to Request!B3:O3.
4 - I selected the data in Request!B10:O10 and Copied it
5 - I selected Request!B3 and did a Paste
6 - I checked the Tracker sheet and the data from Request!B3:O3 was showing in Tracker!B2:O2
7 - I went back to the Request sheet and pasted the data into Request!B3 again.
8 - I checked the Tracker sheet and the data from Request!B3:O3 was showing in Tracker!B3:O3, what was the next empty row.
9 - Each time I do a Paste into Request!B3, the data in Request!B3:O3 is automatically copied to columns Tracker!B:O in the next empty row in that sheet.

Works every time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 16, 2014 at 11:26:51
Thank you so much! :) It's working now. :) Can you please help me with another one? With my another workbook, the same will be done but Only when you clicked a Command Button request area where it will be saved to the tracker and everything entered on the row (at request tab) will be deleted and together with some other cell on request area.

For example: C3 to P3 is the field where I wanted to paste the data (let's name the tab "invoice data file"). It will be saved to the next sheet named "tracker of invoice". A command button was at the end of the page and that is what I want to click when I will save the data entered on C3 to P3 plus cells C4, E4, H4,J4,M4 and P4.

I would greatly appreciate this.


Report •

#7
January 17, 2014 at 06:28:46
Please post your new question in a separate thread, with a relevant subject line.

Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
January 17, 2014 at 12:17:14
Yes I have, please see http://www.computing.net/answers/wi... thank you.

Report •

Ask Question