CODEWITHSUNDEEP
vbaexcel
user1518256
user1518256

Reputation: 1

Pasting data to a new worksheet

I'm new to VBA so am probably asking something really basic - sorry! I have a worksheet with a series of records that I want to move into a different worksheet called "July Archive". I'm using this code and I know that this is going to overwrite the destination cells in "July Archive". But I get an error saying the pasteValue operation failed.

Also, how can I append the cells rather than overwrite. I tried PasteAppend but get an error say its not supported by the object.

Sub Selectweeklyreport()

    ActiveSheet.Range("a16", ActiveSheet.Range("f16").End(xlDown)).Cut
    ActiveSheet.Goto ("July Archive")
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

End Sub

Upvotes: 0

Views: 145

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Like this?

Sub Selectweeklyreport()
    Dim wsI As Worksheet, wsO As Worksheet
    Dim lRow As Long

    '~~> Replace this with the relevant sheet name
    Set wsI = Sheets("Sheet1")
    Set wsO = Sheets("July Archive")

    With wsI
        '~~> Get the last Row in Sheet1
        lRow = .Range("F" & .Rows.Count).End(xlUp).Row

        '~~> Copy the range from Sheet1
        .Range("A16:F" & lRow).Copy 'and not .Cut????

        '~~> Get the next available row in July Archive
        lRow = wsO.Range("A" & wsO.Rows.Count).End(xlUp).Row + 1

        '~~> Paste Special Values
        wsO.Range("A" & lRow).PasteSpecial Paste:=xlValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
End Sub

Upvotes: 1

Related Questions