Copy To 1st Blank Cell

[lorddrezz]

Hey all,

I have an issue where i need to use a VBA code on a button that when clicked it copies the cell next to it and pastes it on another page in the 1st available blank cell. and when i put in another value in that same cell and click the button it would put that value in the next available blank cell (under the 1st one) and so on and so on.

What I have so far is
Sub Copy2Prod()
'
' Copy2Prod Macro
'
'
If Sheets("Production").Cells(A4) = "" Then
Sheets("CORR Quality Comments Template").Select
Range("C4").Select
Selection.Copy
Sheets("Production").Select
Range("A4").Select
ActiveSheet.Paste
Else
Sheets("CORR Quality Comments Template").Select
Range("C4").Select
Selection.Copy
Sheets("Production").Select
Range("A4:A60").Offset(1, 0).Select
ActiveSheet.Paste
End If
End Sub



Excel 2010 is what I have, can anyone assist?

 

[Scott Huish]

What is Cells(A4)??? Do you mean Range("A4")

There is also no need to do all that Selecting, you can work with ranges directly in VBA.

Why are you pasting blank cells? What are you trying to accomplish exactly?

 

[lorddrezz]

Basically in Cell C4 on the 1st sheet (CORR Quality Comments Template) I have a value that i type in. i have a button next to it. i click the button I want it to copy that value, and then look at the range A4 to A60 on the 2nd Sheet (Production) and go down the list to the 1st blank cell and paste it there.

 

[TiaXL]

Basically in Cell C4 on the 1st sheet (CORR Quality Comments Template) I have a value that i type in. i have a button next to it. i click the button I want it to copy that value, and then look at the range A4 to A60 on the 2nd Sheet (Production) and go down the list to the 1st blank cell and paste it there.

range("a4").select
selection.end(xldown).Offset(1,0).select


will take you to first blank in your range

 

[Scott Huish]

Why the Select?

Range("A4").End(xlDown).Offset(1).PasteSpecial

 

[John Davis]

Basically in Cell C4 on the 1st sheet (CORR Quality Comments Template) I have a value that i type in. i have a button next to it. i click the button I want it to copy that value, and then look at the range A4 to A60 on the 2nd Sheet (Production) and go down the list to the 1st blank cell and paste it there.

Would this help?

Sub lorddrezz()
'

Sheets("Production").Range("A" & Rows.Count).End(xlUp)(2).Value = Sheets("CORR Quality Comments Template").Range("C4").Value


End Sub

 

[lorddrezz]

Thanks TIA, works perfectly.