CODEWITHSUNDEEP
vbaexcel
sayth
sayth

Reputation: 7048

PasteSpecial - End of Statement Expected

I have this script that saves my selection to another page. I use cut as part of my tidying the page for the next input.

Anyway I have tried several variation on xlPasteValues however keep receiving end of statement expected.

From Mr Excel I was replicating this however produces the error.

Worksheets("Positions").Range("W2").PasteSpecial xlPasteValues

Tried the variant from the udemy blog

wb.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues

I am just not understanding how those implementations are not encountering the end of statement and mine is.

Sub SaveShrinkage()

    Dim LRow As Long
    Dim ws As Worksheet
    Dim Newcastle_Shrinkage As Range
    Dim ToSite_Shrinkage As Worksheet

    Set ws = ThisWorkbook.ActiveSheet
    LRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row

    Set Newcastle_Shrinkage = ws.Range("A6:E" & LRow)

    Set ToSite_Shrinkage = Worksheets("Site_Shrinkage")

    Newcastle_Shrinkage.Cut Destination:=ToSite_Shrinkage.Range("B" & ToSite_Shrinkage.UsedRange.Rows.Count + 1).PasteSpecial xlPasteValues

End Sub

Upvotes: 1

Views: 3154

Answers (4)

YowE3K
YowE3K

Reputation: 23994

Excel doesn't support (AFAIK) Cut / PasteValues, so you will need to use:

Newcastle_Shrinkage.Copy 
ToSite_Shrinkage.Range("B" & ToSite_Shrinkage.UsedRange.Rows.Count + 1).PasteSpecial Paste:=xlPasteValues
Newcastle_Shrinkage.ClearContents

Note: The answer by Comintern is probably (definitely!) better than mine. Anything that avoids the clipboard, and the associated problems that causes when the user is doing something in the background, is a much, much, much better solution.

Upvotes: 3

user6432984
user6432984

Reputation:

The reason you are getting an error is that the Range().Cut method takes a range as a parameter and Range().PasteSpecial xlPasteValues does not return a range.

This will correct your code.


Newcastle_Shrinkage.Cut Destination:=ToSite_Shrinkage.Range("B" & ToSite_Shrinkage.UsedRange.Rows.Count + 1)

Personally I would refactor the code removing most of the variables that will only be used once and using a more generic name for the source range. I think that the more condense code is actually easier to read.

Cut Range with formatting

Sub SaveShrinkageWithFormat()

    Dim Source As Range

    With ThisWorkbook.ActiveSheet
        Set Source = .Range("A6", .Range("E" & .Rows.Count).End(xlUp))
    End With


    With Worksheets("Site_Shrinkage")
        Source.Cut Destination:=.Range("B" & .Rows.Count).End(xlUp).Offset(1)
    End With

End Sub

Move values no formatting

Sub SaveShrinkageValuesOnly()

    Dim Source As Range

    With ThisWorkbook.ActiveSheet
        Set Source = .Range("A6", .Range("E" & .Rows.Count).End(xlUp))
    End With

    With Worksheets("Site_Shrinkage")
        .Range("B" & .Rows.Count).End(xlUp).Offset(1) = Source.Value
        Source.ClearContents
    End With

End Sub

Upvotes: 3

jamheadart
jamheadart

Reputation: 5343

You're getting the error as you write the code because you're trying to use "Cut" "Destination" and "PasteSpecial" in one line - Destination and PasteSpecial overlap here so you could suggest a start by getting rid of one of these terms to get either:

range("A1").cut destination:=range("A2")

or:

range("A1").cut
range("A2").pastespecial xlpastevalues

however, as flagged in the answer by @YowE3K, "CUT" doesn't work with subsequent "pastespecial" so this second method will only work with .copy as he shows.

The best answer to follow is Comintern's.

Upvotes: 2

Comintern
Comintern

Reputation: 22205

If all you need are the values, you can skip using the clipboard entirely. Just set the values directly, then clear the source Range:

Sub SaveShrinkage()

    Dim LRow As Long
    Dim ws As Worksheet
    Dim Newcastle_Shrinkage As Range
    Dim ToSite_Shrinkage As Worksheet

    Set ws = ThisWorkbook.ActiveSheet
    LRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row

    Set Newcastle_Shrinkage = ws.Range("A6:E" & LRow)

    Set ToSite_Shrinkage = Worksheets("Site_Shrinkage")

    With ToSite_Shrinkage
        Dim rowCount As Long
        rowCount = ToSite_Shrinkage.UsedRange.Rows.Count
        .Range(.Cells(rowCount + 1, 2), _
               .Cells(rowCount + Newcastle_Shrinkage.Rows.Count, 6)).Value = _ 
                   Newcastle_Shrinkage.Value
    End With
    Newcastle_Shrinkage.ClearContents

End Sub

Upvotes: 4

Related Questions