Writing data on last empty cell in a column

[earlyyyyyy]

Hi!
So.. I select a Phase , which gives me the specific structure codes for that phase, then I input the number of sets that are needed. Finally, I press the command button to record the data in the "Data" sheet . It writes the data on the "Data" worksheet in the first column (pcs) However, if I enter another set of data from the "Steel Pole" Worksheet, it overwrites the previously recorded data. My guess is because I assigned numerical values to the row and column in my vba code. Possible fix would be to find the next empty cell in the column and make the next set of data write from there. Any help would be much appreciated

Dropbox file for Reference
https://www.dropbox.com/s/f35c4e07vl...lock.xlsm?dl=0

[BadlySpelledBuoy]

Try this amended version of your code (see attached).

BSB

[Mandeep Baluja]

put this code in command button click event

Private Sub CommandButton1_Click()
Dim Row As Integer
If Range("C15").Value = "" Or Range("C17").Value = "" Then
MsgBox ("Select Structure code and Input Quantity of Materials")
Else
Row = 2
Set ws = ActiveSheet
lr = Application.WorksheetFunction.Count(Range("E15:E50")) + 14
Worksheets("Steel Pole").Range("E14:G" & lr).Copy
lr = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Data").Cells(lr + 1, 1).PasteSpecial Paste:=xlPasteValues
MsgBox ("The data has been recorded!")
Range("C15", "C17").Value = ""
Application.CutCopyMode = False
End If

End Sub

[earlyyyyyy]

Try this amended version of your code (see attached).

BSB

All Right! BSB to the rescue. Exactly what I was looking for. Replicating the codes for the other 2 columns was easy enough. Thanks a ton

[earlyyyyyy]

put this code in command button click event

Private Sub CommandButton1_Click()
Dim Row As Integer
If Range("C15").Value = "" Or Range("C17").Value = "" Then
MsgBox ("Select Structure code and Input Quantity of Materials")
Else
Row = 2
Set ws = ActiveSheet
lr = Application.WorksheetFunction.Count(Range("E15:E50")) + 14
Worksheets("Steel Pole").Range("E14:G" & lr).Copy
lr = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Data").Cells(lr + 1, 1).PasteSpecial Paste:=xlPasteValues
MsgBox ("The data has been recorded!")
Range("C15", "C17").Value = ""
Application.CutCopyMode = False
End If

End Sub

Nice! I tried your code out and it worked flawlessly too. Thanks for this

[BadlySpelledBuoy]

Glad I could help.

If you're transferring more than one column over to the data sheet then Mandeep's copy and paste approach makes far more sense.
I just wanted to show you where your method was going awry.

Thanks for the rep point

BSB