Two issues with macro

[paulsolar]

Hi All

I have been reading up on copying sheets to a newly created workbook and have modified (joined together) info i have found on line.

I'm getting a runtime error 1004 here
Cells(1, 1).Select
ws.Activate

Below is the whole of the code I have come up with for this, but one thing I have noticed is that the pasted sheet still has the formulae in it not the values??

Any ideas where I'm going wrong please

cheers

Paul

Option Explicit
 
Sub Move_New_Workbook()
    Dim NewName As String
    Dim nm As Name
    Dim ws As Worksheet
    Dim MyDate
    MyDate = Date
     
     
   With Application
        .ScreenUpdating = False
         
        On Error GoTo ErrCatcher
        Sheets(Array("MiREG Month By Month", "Tony Morley")).Copy
        On Error GoTo 0
         
        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select
         
         '       Remove named ranges
        For Each nm In ActiveWorkbook.Names
            nm.Delete
        Next nm
         

        NewName = "New MiREG Report:-" & MyDate
        
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
        ActiveWorkbook.Close SaveChanges:=False
         
        .ScreenUpdating = True
    End With
    Exit Sub
     
ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
End Sub

 

[MARK858]

I'm getting a runtime error 1004 here
Cells(1, 1).Select
ws.Activate

What happens with the moving around below

        For Each ws In ActiveWorkbook.Worksheets
            Application.CutCopyMode = False
            ws.Cells.Copy
            ws.[A1].PasteSpecial xlValues
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            ws.Activate
            ws.Cells(1, 1).Select
        Next ws
        'Cells(1, 1).Select 'I don't see why this line is there

 

[paulsolar]

Hi Mark

Sorry the delay in replying, had a very anxious puppy waiting for his walk

Well that is the issue fixed thanks, now it's copying properly

Many thanks for your assistance

cheers

Paul

 

[MARK858]

You're welcome

 

[smozgur]

Well that is the issue fixed thanks, now it's copying properly

Many thanks for your assistance

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.