run-time error '1004' in VBA

[learning_grexcel]

When I run the below code, I get the subject error.

However, when I remove this line from the code - "oldbook.Worksheets("prc").Range("A1").Select" , the code works fine. I'm not sure what the problem is with this line.

Set oldbook = ActiveWorkbook
Set newbook = Workbooks.Add
newbook.Worksheets("sheet1").Name = "org"
oldbook.Worksheets("sheet1").Range("A:A").Copy
newbook.Worksheets("org").Range("A1").PasteSpecial xlPasteValues
oldbook.Worksheets("sheet1").Range("C:C").Copy
newbook.Worksheets("org").Range("B1").PasteSpecial xlPasteValues
newbook.SaveAs Filename:="org2014.xls"
newbook.Worksheets("org").Range("A1").Select


oldbook.SaveAs Filename:="prc2014.xls"
oldbook.Worksheets("sheet1").Name = "prc"
oldbook.Worksheets("prc").Range("A:B").Copy
oldbook.Worksheets("prc").Range("D:E").PasteSpecial (xlPasteValues)
oldbook.Worksheets("prc").Range("A:B").Delete
oldbook.Worksheets("prc").Range("A1").Select
Application.CutCopyMode = False
oldbook.Worksheets("prc").Rows(1).Delete
oldbook.Worksheets("sheet2").Delete
oldbook.Worksheets("sheet3").Delete
newbook.Close savechanges:=True
oldbook.Close savechanges:=True
End Sub

 

[royUK]

The sheet has been renamed so the code needs to use that name

Option Explicit


Sub x()
    Dim newbook As Workbook
    With Application
        .ScreenUpdating = False
        On Error GoTo exit_Proc
        Set newbook = Workbooks.Add




        With newbook
            .Worksheets("sheet1").Name = "org"
            ThisWorkbook.Worksheets("sheet1").Range("A:A").Copy .Worksheets("org").Range("A1")
            ThisWorkbook.Worksheets("sheet1").Range("C:C").Copy .Worksheets("org").Range("B1")
            .Worksheets("org").UsedRange.Value = .Worksheets("org").UsedRange.Value
            .SaveAs Filename:="org2014.xls"
            .Close savechanges:=True
        End With




        With ThisWorkbook
            .SaveAs Filename:="prc2014.xls"
            .Worksheets("sheet1").Name = "prc"
            .Worksheets("prc").Range("A:B").Copy
            .Worksheets("prc").Range("D:E").PasteSpecial (xlPasteValues)
            .Worksheets("prc").Range("A:B").Delete
            .Worksheets("prc").Rows(1).Delete
            .Worksheets("sheet2").Delete
            .Worksheets("sheet3").Delete




            .Close savechanges:=True
        End With
exit_Proc:
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

 

[Teeroy]

Sheet1 has been renamed so it will error if you try to run this code after the rename.

The original example made a copy of the oldworkbook (i.e. this workbook) sheet1 and pasted values only back over itself. This line will do the same in the second With Block if it is done prior to the rename, or if you want it after then rename then change "Sheet1" to "prc".

Sorry RoyUK, I hadn't refreshed so I missed your response.

 

[learning_grexcel]

Thank you RoyUK and Teeroy, now I updated my code. That line is ok but now it stuck at the first ".Close savechanges:=True"

After save, it is not closing the file and stops executing

 

[royUK]

Try this instead

.Close True

 

[learning_grexcel]

Thanks RoyUK, but still same, it's not closing and it stops executing further.

 

[Teeroy]

Comment out the error handling line and see if you get more debugging information.

 

[royUK]

The best thing would be to share the file using DropBox or Cubby