5 Replies Latest reply: Feb 13, 2018 11:34 AM by m w RSS

    PasteSpecial method of Worksheet class fails sometimes

    Mihai Iov

      I'm having a headache with a macro to export the data from some pivot tables to an  Excel template. It throws a "PasteSpecial method of Worksheet class failed" from time to time, not always, but I don't understand why the error is not consistent. Can anyone help me with figuring out what I'm doing wrong?
      Here's the macro:

      sub ExcelTable1TemplateUse

      Dim CountPasteSpecialErrors

      set XLApp = CreateObject("Excel.Application")

      XLApp.Visible = True

      set XLDoc = XLApp.Workbooks.Open("C:\Users\BSS\Documents\Alfaintes\20171204 - Report prioritari\Reports\Tabel 1\Table1_Template_SingleUser.xlsx")

      XLDoc.Sheets(1).Select

      XLDoc.Sheets(1).Range("A1").Select

      ActiveDocument.GetSheetObject("CH128").CopyTableToClipboard true

      XLDoc.Sheets(1).PasteSpecial -4163

      XLDoc.Sheets(1).Range("X1").Value = err.Number

      XLDoc.Sheets(1).Range("A1:C1").ClearContents

      XlDoc.Sheets(1).Range("A1:C1").Merge

      XLDoc.Sheets(1).Range("A2").Value = XLDoc.Sheets(1).Range("A3").Value & " " & XLDoc.SHeets(1).Range("B3").Value

      XLDoc.Sheets(1).Range("B2:B10,A3:A10").ClearContents

      XLDoc.Sheets(1).Range("A2:B2").Merge

      end sub