Help with error 1004, Pastespecial method failed

S

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
365
Hi, I am getting the run-time error 1004, pastespecial method of range class failed when I run the code below. It occurs on the line below. But it doesn't occur on other lines in the code that are written the same way. I am attempting to paste values while keeping the formatting. Thanks
wb2.Sheets("QUOTE").Range("F4").PasteSpecial

VBA Code: 
Sub copyrange4()

Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim Pth As String
    Dim lr As Long
    Set wb1 = ActiveWorkbook
    Pth = wb1.path
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Set wb2 = Workbooks.Open("C:\users\" & Environ("username") & "\Desktop\Dropbox\Quotes Steve\quoteprogram2.xlsm")
   
    lr = wb1.Sheets("QUOTE").Range("C" & Rows.Count).End(xlUp).Row  'last row ?  if determinable from column C  ?
    
    wb1.Worksheets("QUOTE").Range("E1:E3").Copy
    wb2.Sheets("QUOTE").Range("E1").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("D4").Copy
    wb2.Sheets("QUOTE").Range("D4").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("F4").Copy
    wb2.Sheets("QUOTE").Range("F4").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("F6").Copy
    wb2.Sheets("QUOTE").Range("F6").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("F7").Copy
    wb2.Sheets("QUOTE").Range("F7").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("D19:D20").Copy
    wb2.Sheets("QUOTE").Range("D19").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("I21").Copy
    wb2.Sheets("QUOTE").Range("I21").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("K20").Copy
    wb2.Sheets("QUOTE").Range("K20").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("M20").Copy
    wb2.Sheets("QUOTE").Range("M20").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("S21").Copy
    wb2.Sheets("QUOTE").Range("S21").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("EX16:EX17").Copy
    wb2.Sheets("QUOTE").Range("EX16").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("C24:I" & lr).Copy
    wb2.Sheets("QUOTE").Range("C24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("M22").Copy
    wb2.Sheets("QUOTE").Range("M22").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("M24:M" & lr).Copy
    wb2.Sheets("QUOTE").Range("M24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("W24:X" & lr).Copy
    wb2.Sheets("QUOTE").Range("W24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("Z24:AB" & lr).Copy
    wb2.Sheets("QUOTE").Range("Z24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("AG21").Copy
    wb2.Sheets("QUOTE").Range("AG21").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("AD24:AH" & lr).Copy
    wb2.Sheets("QUOTE").Range("AD24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("AI21").Copy
    wb2.Sheets("QUOTE").Range("AI21").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("AJ24:AO" & lr).Copy
    wb2.Sheets("QUOTE").Range("AJ24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("AQ24:BA" & lr).Copy
    wb2.Sheets("QUOTE").Range("AQ24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("BC24:BC" & lr).Copy
    wb2.Sheets("QUOTE").Range("BC24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("BE24:BR" & lr).Copy
    wb2.Sheets("QUOTE").Range("BE24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("BT24:BX" & lr).Copy
    wb2.Sheets("QUOTE").Range("BT24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("DJ24:DL" & lr).Copy
    wb2.Sheets("QUOTE").Range("DJ24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("EL24:EM" & lr).Copy
    wb2.Sheets("QUOTE").Range("EL24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("IY24:JA" & lr).Copy
    wb2.Sheets("QUOTE").Range("IY24").PasteSpecial
    
    wb1.Worksheets("QUOTE").Range("KG24:KG" & lr).Copy
    wb2.Sheets("QUOTE").Range("KG24").PasteSpecial
 
    Range("C24").Select

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Would you like to paste only values?
Your code is as if it were a values paste, but it is actually a copy and paste.

If you want to paste only values.

VBA Code: 
wb2.Sheets("QUOTE").Range("E1:E3").Value = wb1.Worksheets("QUOTE").Range("E1:E3").Value
wb2.Sheets("QUOTE").Range("D4").Value = wb1.Worksheets("QUOTE").Range("D4").Value

or

With wb1.Worksheets("QUOTE")
    wb2.Sheets("QUOTE").Range("E1:E3").Value = .Range("E1:E3").Value
    wb2.Sheets("QUOTE").Range("D4").Value = .Range("D4").Value
End With

If you want to copy/paste.

VBA Code: 
wb1.Worksheets("QUOTE").Range("E1:E3").Copy wb2.Sheets("QUOTE").Range("E1")
wb1.Worksheets("QUOTE").Range("D4").Copy wb2.Sheets("QUOTE").Range("D4")

or
    
    With wb1.Worksheets("QUOTE")
        .Range("E1:E3").Copy wb2.Sheets("QUOTE").Range("E1")
        .Range("D4").Copy wb2.Sheets("QUOTE").Range("D4")
    End Sub
 
Upvote 0
Takae said:
Would you like to paste only values?
Your code is as if it were a values paste, but it is actually a copy and paste.

If you want to paste only values.

VBA Code: 
wb2.Sheets("QUOTE").Range("E1:E3").Value = wb1.Worksheets("QUOTE").Range("E1:E3").Value
wb2.Sheets("QUOTE").Range("D4").Value = wb1.Worksheets("QUOTE").Range("D4").Value

or

With wb1.Worksheets("QUOTE")
    wb2.Sheets("QUOTE").Range("E1:E3").Value = .Range("E1:E3").Value
    wb2.Sheets("QUOTE").Range("D4").Value = .Range("D4").Value
End With

If you want to copy/paste.

VBA Code: 
wb1.Worksheets("QUOTE").Range("E1:E3").Copy wb2.Sheets("QUOTE").Range("E1")
wb1.Worksheets("QUOTE").Range("D4").Copy wb2.Sheets("QUOTE").Range("D4")

or
   
    With wb1.Worksheets("QUOTE")
        .Range("E1:E3").Copy wb2.Sheets("QUOTE").Range("E1")
        .Range("D4").Copy wb2.Sheets("QUOTE").Range("D4")
    End Sub
Click to expand...
Thanks for the help. I'm using the paste only values and it's working better.

Another question - in my original copy and paste below I was pasting a range to C24. However, with the "paste value" line it only pastes into C24 and not through "I". Do I have to specify the range in both wb1 and wb2?

Excel Formula: 
wb1.Worksheets("QUOTE").Range("C24:I" & lr).Copy
    wb2.Sheets("QUOTE").Range("C24").PasteSpecial

below only pasted into C24
Excel Formula: 
wb2.Sheets("QUOTE").Range("C24").Value = wb1.Worksheets("QUOTE").Range("C24:I" & lr).Value

Does it have to be this way? This worked but I though the 'lr" only applies to the wb1 so why would it work in the paste?
Excel Formula: 
wb2.Sheets("QUOTE").Range("C24:I" & lr).Value = wb1.Worksheets("QUOTE").Range("C24:I" & lr).Value

Thanks
 
Upvote 0
VBA Code: 
wb2.Sheets("QUOTE").Range("C24:I" & lr).Value = wb1.Worksheets("QUOTE").Range("C24:I" & lr).Value
Actually the above expression is assignment expression. This is not exactly a paste value. But this way is faster than
VBA Code: 
    Range("A1").Copy
    Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
this way and looks good. (The above code is paste only value)

When using this assignment expression, the left and right sizes must be the same.
Like Range("A1:A3").value = Range("J10:J12").value also it needs "value".

"lr" has just a number. It is not only applies to the wb1. You can use this variable any sheets.
Like
wb2.Sheets("QUOTE").Range("C24:I" & lr).Value = wb1.Worksheets("Sheet1").Range("C30:I" & lr + 6).Value
 
Upvote 0
Solution
Takae said:
VBA Code: 
wb2.Sheets("QUOTE").Range("C24:I" & lr).Value = wb1.Worksheets("QUOTE").Range("C24:I" & lr).Value
Actually the above expression is assignment expression. This is not exactly a paste value. But this way is faster than
VBA Code: 
    Range("A1").Copy
    Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
this way and looks good. (The above code is paste only value)

When using this assignment expression, the left and right sizes must be the same.
Like Range("A1:A3").value = Range("J10:J12").value also it needs "value".

"lr" has just a number. It is not only applies to the wb1. You can use this variable any sheets.
Like
wb2.Sheets("QUOTE").Range("C24:I" & lr).Value = wb1.Worksheets("Sheet1").Range("C30:I" & lr + 6).Value
Click to expand...
Thanks for the detailed explanation. I think I'm on my way now.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Copy range between open workbooks
Replies
4
Views
546
Shadkng
S
T
  • Question
VBA Code - PasteSpecial Method of range failed (Error 1004)
Replies
3
Views
429
MARK858
MARK858
D
  • Solved
Runtime error 1004 - PasteSpecial method of range class failed
Replies
2
Views
2K
danbates77
D
D
  • Solved
Runtime error 1004 - Pastespecial failing me
Replies
6
Views
663
Alex Blakenburg
A
S
  • Question
Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed
Replies
5
Views
3K
Joe4
Joe4

Forum statistics

Threads
1,214,646
Messages
6,120,718
Members
448,986
Latest member
andreguerra

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