PasteSpecial method of Range class failed

[sharonng]

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:

            '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.

 

[Peter_SSs]

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.

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

 

[sharonng]

The error still exists...

 

[sharonng]

I stand corrected. It works!

 

[Peter_SSs]

I stand corrected. It works!

Glad it worked for you. Thanks for letting us know.