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
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