Aida February 2016

Run-time error '1004': Copy method of Range class failed Excel 2013 when adding a new pivot table

I am getting the error specified in the title of this issue when trying to copy and paste some columns (and it's data) to a new workbook. The code below used to work till the moment when I add a new sheet with a new pivot table in my workbook, and I don't know the reason:

Sub ExtractData_2()

  Workbooks.Add
  ActiveWorkbook.SaveAs ThisWorkbook.Path & "\extract_Fcst" & ".csv", 6
  ThisWorkbook.Worksheets("Forecast Enrichment").Activate

  ThisWorkbook.Sheets("Forecast Enrichment").Range("E:S").Copy Destination:=Workbooks("extract_Fcst.csv").Sheets(1).Range("A:O")

  Workbooks("extract_Fcst.csv").Sheets(1).Range("A:O").EntireColumn.AutoFit

End Sub

Does anybody have any idea to how to solve that problem? I have tried a lot of different solutions found in google but any of it works!

Answers


David Zemens February 2016

Use object variables and assign properly, then break up your copy/Destination to see whether the error raises on the Copy or the Paste, as follows:

Sub ExtractData_2()

  Dim csvWorkbook as Workbook

  Set csvWorkbook = Workbooks.Add
  csvWorkbook.SaveAs ThisWorkbook.Path & "\extract_Fcst" & ".csv", 6

  ' Unnecessary to "Activate" the sheet...", so you can delete this:
  ' ThisWorkbook.Worksheets("Forecast Enrichment").Activate

  'Try using copy/paste as separate statements to see where the failure may occur
  ThisWorkbook.Sheets("Forecast Enrichment").Range("E:S").Copy
  csvWorkbook.Sheets(1).Range("A1").Select
  csvWorkbook.Sheets(1).Paste

  csvWorkbook.Sheets(1).Range("A:O").EntireColumn.AutoFit

End Sub

If it still raises the error, let me know which line it happens.

Post Status

Asked in February 2016
Viewed 1,258 times
Voted 10
Answered 1 times

Search




Leave an answer