2

I have the following code in order to copy a sheet from a workbook and paste it on the sheet 1 of another workbook called "Control_de_precios":

Sub createSpreadSheet()

Set NewBook = Workbooks.Add
With NewBook
    .Title = "Control_precios_ddmmaaaa"
    .Subject = "Control_de_precios"
    .SaveAs Filename:="Control_precios_ddmmaaaa.xls"
End With

ThisWorkbook.Worksheets(1).Activate
Cells.Select
Selection.Copy
NewBook.Sheets(1).Activate
ActiveSheet(1).PasteSpecial xlPasteValues

End Sub

The problem is that I get the 438 error because the last instruction, and I don't get to paste values on my new workbook. If I change it for:

ActiveSheet(1).Paste

I don't get the 438 error, and I get to paste the formulas, but I want to paste the values.

¿Could anyone help me?

Mauro
  • 477
  • 1
  • 9
  • 22
  • 1
    ActiveSheet(1).Paste works for you? I find that strange, because it seems like mix of "ActiveSheet" and "Sheets(1)". Have you tried removing the "(1)"? – Pavel_V Aug 29 '17 at 11:25
  • Yes sorry, my original code has: `ActiveSheet.Paste ActiveSheet.Cells.Select Selection.Copy ActiveSheet.PasteSpecial xlPasteValues End Sub` But I still get the 438 error. – Mauro Aug 29 '17 at 11:29
  • What is the error message for 438? – David Rushton Aug 29 '17 at 11:29
  • "This object does not admit this property or method" I think it refers to the PasteSpecial method. – Mauro Aug 29 '17 at 11:32
  • Try adding Range reference after the activesheet. When I do it this way it works. ActiveSheet.Range("A1").PasteSpecial xlPasteValues. – Pavel_V Aug 29 '17 at 11:39
  • It worked, thank you, you can put this as an answer if you want me to accept it. – Mauro Aug 29 '17 at 11:46

2 Answers2

1

Add Range reference after the activesheet. When I do it this way it works. ActiveSheet.Range("A1").PasteSpecial xlPasteValues

Pavel_V
  • 1,220
  • 1
  • 11
  • 17
1

Write 

Selection.PasteSpecial xlPasteValues

instaed of

ActiveSheet(1).PasteSpecial xlPasteValues
Yura
  • 969
  • 14
  • 33