Help with moving data to an archive sheet

[fish0321]

Hi all,

First, thanks for the help, I really appreciate all you EXCEL pros for taking the time to help us Noobs. I have is pieced together a bit of code to move data from one sheet to another when a certain condition is met. I have two sheets in the same workbook, one TEST sheet and one ARC DATA sheet. The code i used moves data from the TEST sheet to the ARC DATA sheet when a "Y" is present in a specified column (column P) and I press a button. The problem I am having is that when the data is copied over to the ARC DATA sheet it also copies any formulas I have in the cells. Also, i don't know if this modified formula is the most efficient coding for the job. any help would be great, i have included an example workbook.


I would like to move just the cell data and not any codes within the cells.


Here is a copy of the current code I am using for the command button to move data:

Private Sub CommandButton1_Click()
Dim r As Long, lr As Long
r = 2
lr = Cells(Rows.Count, 1).End(xlUp).Row
Do Until r = lr + 20
If Cells(r, 16) = "y" Then
Range(Cells(r, 1), Cells(r, 11)).Copy
Worksheets("ARC DATA").Select
erow = Worksheets("ARC DATA").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Paste Destination:=Worksheets("ARC DATA").Rows(erow)
End If
r = r + 1

[maniacb]

Try this code instead of your current code

Please Login or Register  to view this content.

[fish0321]

Thank You, the code works great. I do have one other question. Is there a way that once i push the button that it will take me to the ARC DATA page to verify the data has been transferred.

[maniacb]

This updated code will do that for you

Please Login or Register  to view this content.