CODEWITHSUNDEEP
vbaexcel
Snflq
Snflq

Reputation: 3

Copy Special Cells to next empty row on another sheet

I am trying to copy rows with numbers to another worksheet. I have obtained the SpecialCells code from another forum but have problems pasting the data into another sheet. Thank you for your help!

Here is the code I have:

Sub Sample()

Dim ws As Worksheet
Dim rng As Range
Dim ws1 As Worksheet

On Error GoTo Whoa

Set ws = Sheets("3")
Set ws1 = Sheets("Sheet1")

With ws
    Set rng = .Cells.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow

    Copy ws1.Range("A" & lastrow)
End With

Exit Sub
End sub

Upvotes: 0

Views: 380

Answers (3)

user3598756
user3598756

Reputation: 29421

You could try this:

Sub Sample()
    With Sheets("Sheet1")
        Sheets("3").UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    End With
End sub

Upvotes: 1

Jacob Ukken
Jacob Ukken

Reputation: 46

Please try the code below.This will copy data from sheet named "3" to "sheet1".

Sub Sample()

Dim ws As Worksheet
Dim rng As Range
Dim ws1 As Worksheet

Set ws = Sheets("3")
Set ws1 = Sheets("Sheet1")
LastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row

Set rng = ws.Cells.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow
rng.Copy
ws1.Activate
Range("A" & LastRow + 1).Select
ActiveSheet.Paste

End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

I don;t see anywehre in your code that you defines and set lastrow.

Modify your last section to:

With ws
    Set Rng = .Cells.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow

    lastrow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row + 1 ' <-- get the last row in column A '<-- just in case you are looking for the first empty row in Column A 
    Rng.Copy ws1.Range("A" & lastrow) '<-- you need to add the Rng.Copy
End With

Upvotes: 0

Related Questions