CODEWITHSUNDEEP
excelvbacopypaste
Lauren Holm
Lauren Holm

Reputation: 1

Copying and pasting a cell into the next empty row in a column

I am copying and pasting some data into an excel sheet. I have three different cells that I need copied and pasted into their corresponding three new cells. I want to be able to paste them into the sheet, and run a macro that copies and pastes them automatically into the empty row and column that I specified. I'm going to need to copy and paste them into the next subsequent empty row out of the same column the next time, so I need to update it somehow or have a condition for it to parse down the column to the next empty cell. I have run this code shown and it gives me a "Subscript Out of Range Error" or a "Can't Jump to Sheet" error. I'm not quite sure what to do.

Sub CopyStuff()
Range("A2:C2").Copy
Sheets("Sheet11").Range("A" & Rows.Count).End(xlUp).Offset(1,0).PasteSpecial xlPasteValues
End Sub

Upvotes: 0

Views: 965

Answers (1)

Cameron Critchlow
Cameron Critchlow

Reputation: 1827

Often times easier to use either name or codename depending on situation.

enter image description here Either

Thisworkbook.sheet1.range("A1").value 'Name
'  -or- 
Thisworkbook.sheets(1).range("A1").value 'Index 
'  -or- 
Thisworkbook.sheets("Sheet2").range("A1").value 'Codename

Anway... double check whether you're trying to reference sheets(11), sheet11 or sheets("Sheet11")

Sub CopyStuff()
    Sheet11.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3) = _
        ActiveSheet.Range("A2").Resize(1, 3).Value
End Sub

Upvotes: 0

Related Questions