VBA to paste into next empty cell in a row

JACOBB9900

New Member
Joined
Apr 8, 2019
Messages
38
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

rollis13

Active Member
Joined
Jul 30, 2012
Messages
417
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Apr 8, 2019
Messages
38
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

Active Member
Joined
Jul 30, 2012
Messages
417
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

rollis13

Active Member
Joined
Jul 30, 2012
Messages
417
Office Version
  1. 2016
Platform
  1. Windows
Glad I was able to help (y).
 

Watch MrExcel Video

Forum statistics

Threads
1,128,075
Messages
5,628,498
Members
416,322
Latest member
Corbett

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top