I would like to copy and paste special values in excel using VB6 during report generation. But pastespecial is not working. Its throwing error 1004 Paste Special method worksheet failed.

Below is the code. can you please advise

xlTmp.ActiveSheet.Range("H7:H23").Select
xlTmp.Selection.Copy
xlTmp.Sheets("Consolidation").Activate
xlTmp.ActiveSheet.Cells(6, 4).Value = P(N)
xlTmp.ActiveSheet.Cells(8, PS).Select
xlTmp.ActiveSheet.PasteSpecial xlPasteValues, xlNone, False, False

I think the problem might be with the range not specified. Try the following -

xlTmp.Activesheet.Range("A1").PasteSpecial Paste:=xlValues, xlNone, False, False

Only a pleasure. Happy coding.:)