CODEWITHSUNDEEP
vbaexcel
MilesToGoBeforeISleep
MilesToGoBeforeISleep

Reputation: 183

Pasting on the next row in another worksheet

I am new to programming and trying to learn the basics of VBA by using the macro recorder. I am working on a small VBA project and need to copy data from Column C, Sheet 2 and Col. H, Sheet 1, then paste them as a single column on Sheet 3, Col A. The first part was simple. I copied from Col C on Sheet 2 to Col A on Sheet 3. The second part is copying from Col. H on Sheet 1, then finding the next available row on Col A on Sheet 3. My question is how do I get my macro to paste the copied data onto the next available row on Sheet 3, Col A? I’m trying to debug and it looks like the problem is with selecting the next empty row. It doesn't look like Cells.NextRow.Select is the proper syntax even though NextRow is defined. Here is my code:

Sub Macro2()

' copy from Sheet2

Sheets("Sheet2").Select

Range("C7").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

' paste on Sheet3

Sheets("Sheet3").Select

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

' copy from Sheet1

Sheets("Sheet1").Select

Range("H2").Select

Range(Selection, Selection.End(xlDown)).Select

Application.CutCopyMode = False

Selection.Copy

' paste on Sheet3

Sheets("Sheet3").Select

NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

**Cells.NextRow.Select**

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub

FYI Sheets 1 and 2 has column headers on rows 1 and 6 respectively. I don't want those copied so that's why I have hard coded the starting point of the range to be copied. My variable "NextRow" seems to be working when I debug but it seems the problem is with the actual selection of the next available row.

Upvotes: 3

Views: 1846

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

NextRow is a number not a Range().

Sheets("Sheet3").Range("A" & NextRow).Select

But you should really clean up your code and avoid using .Select. I'll work on an edit to help.

Sub Macro2()

' copy from Sheet2
    With Sheets("Sheet2")
        .Range(.Range("C7"), .Range("C7").End(xlDown)).Copy
    End With

' paste on Sheet3
    Sheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' copy from Sheet1
    With Sheets("Sheet1")
        .Range(.Range("H2"), .Range("H2").End(xlDown)).Copy
    End With

' paste on Sheet3
    With Sheets("Sheet3")
        NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Range("A" & NextRow)..PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With

End Sub

Upvotes: 1

Related Questions