CODEWITHSUNDEEP
vbaexcel
camelCaseCowboy
camelCaseCowboy

Reputation: 986

cutting from one page to another without select

I am trying to copy from one worksheet to another, and the .End property should work with the Range value, but the error saying it isn't! The developer docs haven't helped, so I'm not sure what to do.

Code below, the .End(xlup) is what's throwing the error.

  Dim inputcells As Range

Sheets(1).Range("B8:F8").Cut


With Worksheets(2).Range("C1000")
    .End (xlUp)
    .Offset(1, 0).PasteSpecial xlPasteValues

End With

Upvotes: 0

Views: 43

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

This will do it,

Sheets(1).Range("B8:F8").Cut Destination:=Sheets(2).Cells(Sheets(2).Rows.Count, "C").End(xlUp).Offset(1)

If you are trying to paste special values with "Cut" it will not work, you can only paste special with "Copy"

One way to get around that is to set the ranges then make the range equal the other range.

Sub Button1_Click()
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim CrNg As Range
    Dim LstRw As Long
    Dim PrNg As Range

    Set sh1 = Sheets(1)
    Set sh2 = Sheets(2)
    Set CrNg = sh1.Range("B8:F8")

    With sh2
        LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
        Set PrNg = .Range(.Cells(LstRw, "C"), (.Cells(LstRw, "G")))
    End With

    PrNg.Value = CrNg.Value

End Sub

You can clear CrNg at the end if you want.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

Use Copy rather than Cut:

Sub ljsh()
    Sheets(1).Range("B8:F8").Copy
    Sheets(2).Range("C1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub

Upvotes: 1

Related Questions