PasteSpecial method of Range class failed

sharonng

New Member
Joined
Jul 14, 2021
Messages
18
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
I encountered a problem where 1) I cannot paste the copied contents into the desired cell, and 2) an error 1004 PasteSpecial method of Range class failed popped up.
Here's my code:
VBA Code:
            'copy whole content from old quarter file with header added
            Windows("Report002_" & year & "Q" & quarter & ".xls").Activate
            Rows(1).Insert
            Range("a1").Value = year & "Q" & (quarter + 1)
            Range("a1:f1442").Select
            Selection.Copy
            
            
            'paste content to new quarter file and close the old quarter file
            Windows("Report002_" & year & "Q" & (quarter + 1) & ".xls").Activate
            Rows(1).Insert
            Columns(1).Insert
            Range("G1").Value = year & "Q" & quarter
            Range("G2").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            ActiveWorkbook.Save
            Windows("Report002_" & year & "Q" & quarter & ".xls").Close
For problem 1), the copied contents were copied into cell H1 instead of cell G2 (which is the desired cell). It also didn't work when I changed 'selection' to 'Range("G2")'. Please help.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,725
Office Version
  1. 365
Platform
  1. Windows
It is not exactly clear to me the result you want but hopefully this will get you closer.

First, 'Year' is a function in vba and you appear to be using it as a variable name. That is a bad idea. I suggest that you change that variable to 'Yr' or similar instead.

Once you have copied the range of interest and then 'Insert' in the other workbook, the copied values will be placed where that insert took place. I think it should be something more like this.

VBA Code:
Windows("Report002_" & Yr & "Q" & (quarter + 1) & ".xls").Activate
Rows(1).Insert
Columns(1).Insert

'copy whole content from old quarter file with header added
Windows("Report002_" & Yr & "Q" & quarter & ".xls").Activate
Rows(1).Insert
Range("a1").Value = Yr & "Q" & (quarter + 1)
Range("a1:f1442").Copy

'paste content to new quarter file and close the old quarter file
Windows("Report002_" & Yr & "Q" & (quarter + 1) & ".xls").Activate
Range("G1").Value = Yr & "Q" & quarter
Range("G2").PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Save
Windows("Report002_" & Yr & "Q" & quarter & ".xls").Close
 
Solution

Forum statistics

Threads
1,147,635
Messages
5,742,250
Members
423,717
Latest member
rubthenut

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
Top