Paste Special Method of Range class failed error while trying to save worksheet

Question

I'm trying to copy a worksheet data and save it in a new worksheet without copying the underlying formulas from the original sheet(shtAnalysis). I'm unable to do that as I'm getting error:

Paste Special Method of Range class failed

at the line wsPaste.UsedRange.PasteSpecial xlPasteValues.

Public Sub PrepareFileAttachment()
    Application.CalculateFull
    Dim wrkBook As Workbook, wsPaste As Worksheet
    Dim Path As String
    Set wrkBook = Workbooks.Add
    Set wsPaste = wrkBook.Worksheets(1)
    Path = "C:\RandomPath" & "\" & "Report" & Format(Now, "mmddyyyy")
    shtAnalysis.Copy
    wsPaste.Activate
    wsPaste.UsedRange.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ActiveWorkbook.SaveAs filename:=Path, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    shtControl.Range(GENERATED_FILENAME).Value = Path & ".xlsx"
    ActiveWindow.Close
End Sub

score 0 · Answer 1 · answered Aug 08 '19 at 15:21

0

Avoid the Activate and the Used.Range, thus, change these lines:

shtAnalysis.Copy
wsPaste.Activate
wsPaste.UsedRange.PasteSpecial xlPasteValues

To these:

shtAnalysis.Copy
wsPaste.PasteSpecial xlPasteValues

The original code is trying to copy the whole worksheet shtAnalysis, but is allowed to paste it only in the UsedRange, which is something that VBA does not like.

answered Aug 08 '19 at 15:21

Vityata

42,633
8
55
100

Thanks for your reply. Unfortunately this does not work. This copies the original content to a different file with the formulas and pasted the text "wsPaste.UsedRange.PasteSpecial xlPasteValues" in a new workbook. – Vibrant Learner Aug 08 '19 at 15:36
@Karam - you probably did something wrong, because if you replaced exactly as mentioned, then the word `UsedRange` could not be on the text as far as it would not exist in the code. Try to restart your PC and give it a new try. – Vityata Aug 08 '19 at 15:40

score 0 · Answer 2 · answered Aug 08 '19 at 15:46

Please give this a try

Public Sub PrepareFileAttachment()
    Application.CalculateFull
    Dim wrkBook As Workbook, wsPaste As Worksheet
    Dim openedWorkbook As Workbook
    Dim Path As String
    Set openedWorkbook = ThisWorkbook
    Set wrkBook = Workbooks.Add
    Set wsPaste = wrkBook.Worksheets(1)
    Path = "C:\RandomPath" & "\" & "Report" & Format(Now, "mmddyyyy")
    openedWorkbook.Sheets("shtAnalysis").Copy
    wsPaste.Activate
    wsPaste.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ActiveWorkbook.SaveAs Filename:=Path, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    shtControl.Range(GENERATED_FILENAME).Value = Path & ".xlsx"
    ActiveWindow.Close
End Sub

Paste Special Method of Range class failed error while trying to save worksheet

2 Answers2