Solved Copy data entered to next blank with a button

January 16, 2014 at 18:34:21
Specs: Windows 7
With my workbook, I want my data entered to a different sheet but only when you clicked a Command Button on the first sheet where it will be saved to the second sheet and everything entered on the row (at request tab) will be deleted and together with some other cell on the first sheet.

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 transfer the data entered on C3 to P3 to the "tracker invoice" plus cells C4, E4, H4,J4,M4 and P4.

I would greatly appreciate this.

See More: Copy data entered to next blank with a button

Report •

January 17, 2014 at 12:32:39
Why did you post your question in the Windows 7 forum? Excel related questions should be posted in the Office Software forum.

It's not clear to me what you are trying to do.

Tell me if this is correct:

1 - You want a macro assigned to a button on a sheet named "invoice data file".

2 - When this button is clicked, you want the following range to be copied to a sheet named "tracker of invoice":


3 - You then want that same range cleared on the "invoice data file" sheet.

Assuming the above is correct, where on the "tracker of invoice" sheet do you want the data pasted?

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

message edited by DerbyDad03

Report •

January 17, 2014 at 12:58:58
Yes, that is correct. :) I want all data copied to be pasted on an entire row, lets say B2 onwards, then the next data entered on "invoice data file" will be on the next blank row.

Report •

January 18, 2014 at 09:45:45
✔ Best Answer
Since neither VBA nor Excel can copy a non-contiguous range of cells, I split your range into a contiguous range (C3:P3) and a non-contiguous range (C4,E4,H4,J4,M4,P4). The contiguous range can be copied with a single operation, but you must loop through the non-contiguous range and copy each cell one at a time.

Create your button and assign this code to it:

Sub CopyRange()
'Determine next empty Row on "tracker of invoice" sheet
 nxtRw = _
  Sheets("tracker of invoice").Range("B" & Rows.Count).End(xlUp).Row + 1
'Copy contiguous range "invoice data file!C3:P3" to "tracker of invoice!B & nxtRw"
 Sheets("invoice data file").Range("C3:P3").Copy _
  Destination:=Sheets("tracker of invoice").Range("B" & nxtRw)
'Loop through non-contiguous range "invoice data file!C4,E4,H4,J4,M4,P4"
'Copy/Paste to "tracker of invoice!B & nxtRw + 1"
 For Each sCell In Sheets("invoice data file").Range("C4,E4,H4,J4,M4,P4")
  sCell.Copy _
   Destination:=Sheets("tracker of invoice").Cells(nxtRw + 1, sCell.Column - 1)
 Next rCell
End Sub

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

Report •

Related Solutions

Ask Question