Solved VBA code with my command button

March 24, 2014 at 18:08:17
Specs: Windows 7
I enter my data on sheet one, from column A to column N. Rows are depending on volume of requests I get for every batch. A command button I have created was named "complete". Everytime I click it, I wanted my data entered on sheet1 to be moved to the next blank row of sheet2 and be saved.

Please help me have a VBA code to make this work be done.

Thank you so much in advance

See More: VBA code with my command button

March 25, 2014 at 07:35:06
✔ Best Answer
The easy way to do this is to set the code to copy the last row entered on Sheet 1. In other words, enter your data in the nest open Row, Columns A:N and when you are happy with what you entered, click the button.

Another option is to copy all of the newly entered data (multiple rows) with one click. The issue with this method is that the code needs to know where to start copying and that can be problematic. If there is something different about the new data that the code can use to determine what is new vs. old that makes the task easier. For example, the code could look at the data in Sheet 2:Column A:last Row and find that data in Sheet 1:Column A. Everything after that would be consider "new". The problem with that method is that Sheet 1:Column A might contain the same data multiple times in the old data so the code really couldn't tell old from new.

If there will always be a cell within A:N that will never be the same, such as a date or a line number, then the multiple row copy might work.

For now, here is some simple code that will copy the last row with each click of the command button:

Sub CopyToSheet2()
'Determine last row with data in Sheet 1
   srcRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Determine last row with data in Sheet 2
   dstRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
   Sheets(1).Range("A" & srcRw).EntireRow.Copy _
      Destination:=Sheets(2).Range("A" & dstRw)
End Sub

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

Report •
Related Solutions

Ask Question