ActiveSheet.Paste Runtime error 1004

Question

I have vba code which helps copying one of the sheets (Having data, text, formula, chart and shapes) from one workbook into new workbook. Part of the code is listed below:

        Thisbook = ActiveWorkbook.Name

        Newbook = "Project " & ActiveSheet.Name & ".xlsx"

        Workbooks.Add

        ActiveWorkbook.SaveAs Filename:=Newbook

        Windows(Thisbook).Activate

        Cells.Select

        Selection.Copy

        Windows(Newbook).Activate

        ActiveSheet.Paste       (Fails)

Runtime error 1004 is troubling. On another module similar code works.

Please provide help.

Answer 1

Using the variables from my previous reply:

wshNew.Range("C12:D38").Value = wshCur.Range("C12:D38").Value

Answer 2

You could use

ActiveSheet.Range("A1").Paste

but here is a slightly more efficient version:

    Dim wshCur As Worksheet

    Dim wbkNew As Workbook

    Dim wshNew As Worksheet

    ' Reference to the active sheet

    Set wshCur = ActiveSheet

    ' Create a workbook with one worksheet

    Set wbkNew = Workbooks.Add(xlWBATWorksheet)

    ' Reference to the new worksheet

    Set wshNew = wbkNew.Worksheets(1)

    ' Copy

    wshCur.UsedRange.Copy Destination:=wshNew.Range("A1")

    ' Clear the clipboard

    Application.CutCopyMode = False

Answer 3

Using the variables from my previous reply:

wshNew.Range("C12:D38").Value = wshCur.Range("C12:D38").Value

It makes a big difference when we look towards experts. Thanks.

Answer 4

You could use

ActiveSheet.Range("A1").Paste

but here is a slightly more efficient version:

    Dim wshCur As Worksheet

    Dim wbkNew As Workbook

    Dim wshNew As Worksheet

    ' Reference to the active sheet

    Set wshCur = ActiveSheet

    ' Create a workbook with one worksheet

    Set wbkNew = Workbooks.Add(xlWBATWorksheet)

    ' Reference to the new worksheet

    Set wshNew = wbkNew.Worksheets(1)

    ' Copy

    wshCur.UsedRange.Copy Destination:=wshNew.Range("A1")

    ' Clear the clipboard

    Application.CutCopyMode = False

Wow. It works. I was in trouble for last four days.

There is a new problem in the subsequent portion of the code. At some places I wish to replace formula with values. I am switching between the CurrentFile (Thisbook) and New Book. It fails while pasting special. I would be really grateful for the help.

        Thisbook.Activate

        Range("C12:D38").Select

        Application.CutCopyMode = False

        Selection.Copy

        Newbook.Activate

        Range("C12").Select

        Selection.PasteSpecial Paste:=xlPasteValues (Fails Error 1004 Paste Special does not work)