VBA to paste into next empty cell in a row

[JACOBB9900]

I'm a total green horn to VBA so I used "Record Macro" to do what I needed to do. I'll first explain what I'm trying to do. I have an "Inventory Dashboard" tab and a "Stock Tracker" tab. I created this so our employees can check items out of our stock room. The "Dashboard" tab is locked with only the required cells that need to be filled left unlocked, for example, the description, P/N, and employee name. I placed a "Submit" button with the macro attached to it so once they fill the sheet out they click the button and the macro copies the information from the Dashboard tab and pastes it into the stock tab top to bottom, then deletes the information in the Dashboard tab to make room for a new entry. The issue I'm having is that it only pastes to the first row. I need the macro to paste the data into the next empty row below so I can keep a count of what has been checked out of the stock room. The macro I have is pasted below. Please let me know if there is any other information you could use from me to help in this situation.

Thank you.

Sub Copy_to_Stock()
'
' Copy_to_Stock Macro
' Copies data from Dashboard and pastes it into stock tab then deletes data in Dashboard.
'

'
Range("D10").Select
Selection.Copy
Sheets("Stock").Select
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Dashboard").Select
Range("D13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock").Select
Range("D8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Dashboard").Select
Range("F16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock").Select
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Dashboard").Select
Range("D16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock").Select
Range("F8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Dashboard").Select
Range("F13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock").Select
Range("G8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Dashboard").Select
Range("F16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock").Select
Range("H8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Dashboard").Select
Range("D10,D13,D16,F10,F13,F16").Select
Range("F16").Activate
Application.CutCopyMode = False
Selection.ClearContents
End Sub

 

[rollis13]

I trimmed the redundant lines of your macro (that's the Recorder) and added a variable to detect the last row in your sheet "Stock".
See if it does the job.

Option Explicit
Sub Copy_to_Stock()
    '
    ' Copy_to_Stock Macro
    ' Copies data from Dashboard and pastes it into stock tab then deletes data in Dashboard.
    '
    Dim lr     As Long                            'last row in Stock
    Application.ScreenUpdating = False
    lr = Sheets("Stock").Range("C" & Rows.Count).End(xlUp).Row + 1
    If lr < 8 Then lr = 8
    With Sheets("Dashboard")
        .Range("D10").Copy
        Sheets("Stock").Range("C" & lr).PasteSpecial Paste:=xlPasteValues
        .Range("D13").Copy
        Sheets("Stock").Range("D" & lr).PasteSpecial Paste:=xlPasteValues
        .Range("F16").Copy
        Sheets("Stock").Range("E" & lr).PasteSpecial Paste:=xlPasteValues
        .Range("D16").Copy
        Sheets("Stock").Range("F" & lr).PasteSpecial Paste:=xlPasteValues
        .Range("F13").Copy
        Sheets("Stock").Range("G" & lr).PasteSpecial Paste:=xlPasteValues
        .Range("F16").Copy
        Sheets("Stock").Range("H" & lr).PasteSpecial Paste:=xlPasteValues
        .Range("D10,D13,D16,F10,F13,F16").ClearContents
        Application.CutCopyMode = False
    End With
    Application.ScreenUpdating = True
End Sub

 

[JACOBB9900]

rollis13, you are a life saver! This worded perfectly! Thank you very much!

 

[JACOBB9900]

rollis13 I ran into another issue I was hoping you could help me with. The Stock sheet needs to remain locked and I'd like to keep it hidden but when running the macro Excel is telling me it can't copy and paste the data into a locked sheet. Do you know a way around this? I appreciate all the help.

 

[rollis13]

All you had to do was a simple search in the web, you would have found a lot of suggestions.
Anyway, you must add at the beginning of the macro:
Sheets("Stock").Unprotect
and at the end:
Sheets("Stock").Protect
If you are using a password just add it after both lines, something like this:
Sheets("Stock").Unprotect Password:="hereyourpassword"

 

[JACOBB9900]

Thank you rollis13.

 

[rollis13]

Glad I was able to help .