error 1004 Paste method of worksheet failed

H

HeatherF

New Member
Joined
Mar 3, 2020
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I created a macro by recording a number of copy & paste operations and it was working, but since reopening the spreadsheet now I'm getting Run time error 1004: Paste method of worksheet Class failed. Debug is highlighing "ActiveSheet.Paste". Any idea why this is happening?
Thank you!

VBA Code: 
Sub GroupAffiliates()
'
' GroupAffiliates Macro
' combine prospects in more than 1 affiliation
'

'
   
    Cells.Select
    ActiveWorkbook.Worksheets("Affinity Network").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Affinity Network").Sort.SortFields.Add2 Key:=Range("B2:B454" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ActiveWorkbook.Worksheets("Affinity Network").Sort
        .SetRange Range("A1:AW454")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Range("AW1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-47]=R[1]C[-47],RC[-48]<>R[1]C[-48]),RC[-48]&"", ""&R[1]C[-48],RC[-48])"
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW" & LastRow)
    Range("AW1:AW" & LastRow).Select
    Columns("AW:AW").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("AW1").Select
    ActiveSheet.Paste
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW" & LastRow)
    Range("AW1:AW" & LastRow).Select
    Columns("AW:AW").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("AW1").Select
    ActiveSheet.Paste
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW" & LastRow)
    Range("AW1:AW" & LastRow).Select
    Columns("AW:AW").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("AW1").Select
    ActiveSheet.Paste
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW" & LastRow)
    Range("AW1:AW" & LastRow).Select
    Columns("AW:AW").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-47]=R[1]C[-47],RC[-48]<>R[1]C[-48]),RC[-48]&"", ""&R[1]C[-48],RC[-48])"
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW" & LastRow)
    Range("AW1:AW" & LastRow).Select
    Columns("AW:AW").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-47]=R[1]C[-47],RC[-48]<>R[1]C[-48]),RC[-48]&"", ""&R[1]C[-48],RC[-48])"
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW" & LastRow)
    Range("AW1:AW" & LastRow).Select
    Columns("AW:AW").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("AW1").Select
    ActiveCell.FormulaR1C1 = "=RemoveDupeWords(RC[-48], "", "")"
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW" & LastRow)
    Range("AW1:AW" & LastRow).Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    ActiveSheet.Range("$A$1:$AW$455").RemoveDuplicates Columns:=Array(2, 3, 4, 5, 6, 7 _
        , 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34 _
        , 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49), Header:=xlYes
End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Never mind, I figured it out!!! Well, I'm still not sure why the first one wasn't working, but I did realize all the pasting I was doing into AW was unecessary. When I re-recorded without that it works fine!
Thanks to everyone who read the post.

New code:
VBA Code: 
Sub GroupAff()
'
' GroupAff Macro
'

'
    ActiveWorkbook.Worksheets("Affinity Network").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Affinity Network").Sort.SortFields.Add2 Key:=Range _
        ("B2:B457"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Affinity Network").Sort
        .SetRange Range("A1:AW457")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AW1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-47]=R[1]C[-47],RC[-48]<>R[1]C[-48]),RC[-48]&"", ""&R[1]C[-48],RC[-48])"
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW452")
    Range("AW1:AW452").Select
    Columns("AW:AW").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("AW1").Select
    ActiveCell.FormulaR1C1 = "=RemoveDupeWords(RC[-48], "", "")"
    Range("AW1").Select
    Selection.AutoFill Destination:=Range("AW1:AW452")
    Range("AW1:AW452").Select
    Columns("AW:AW").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AW:AW").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll Down:=183
    Cells.Select
    Range("A184").Activate
    ActiveSheet.Range("$A$1:$AW$458").RemoveDuplicates Columns:=Array(2, 3, 4, 5, 6, 7 _
        , 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34 _
        , 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49), Header:=xlYes
End Sub
 
Upvote 0
HeatherF said:
I'm still not sure why the first one wasn't working,
Click to expand...
The error you were getting was most likely due to this line:
VBA Code: 
Application.CutCopyMode = False
preceding your error line
VBA Code: 
ActiveSheet.Paste
which means there was nothing to paste
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
155
Coyotex3
C
P
  • Solved
Counter in For Each loop
Replies
2
Views
192
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
169
pennbowl
P
G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
362
rlv01
R
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
303
Azamamir
A

Forum statistics

Threads
1,214,798
Messages
6,121,630
Members
449,041
Latest member
Postman24

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top