copy multiple sheets and past as values in a new workbook VBA

[DavyJones90]

Hey there,
I don't get exactly what I need to change to make this code work. I need two worksheets to be copied to a new workbook and pasted as values. This is my code.

Public Sub Modelo()

    Dim newWorkbook As Workbook
    Dim newWbPath As String: newWbPath = ThisWorkbook.Path & "\MODELOMNI DATA.xlsx"
    Set newWorkbook = Workbooks.Add
  

  
    ThisWorkbook.Worksheets("AUTO ENTRY POINTS").Range("O4").NumberFormat = "dd/mm/yy"
    ThisWorkbook.Worksheets("AUTO ENTRY POINTS").Range("O4").Value = Date
    ThisWorkbook.Worksheets("AUTO ENTRY POINTS").Cells.Copy
    newWorkbook.Worksheets(1).Cells.PasteSpecial Paste:=xlPasteFormats
    newWorkbook.Worksheets(1).Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    newWorkbook.Worksheets(1).Range("A:A").ClearContents
    newWorkbook.Worksheets(1).Name = "Values"
    ThisWorkbook.Worksheets("XL FILE").Cells.Copy
    newWorkbook.Worksheets(2).Cells.PasteSpecial Paste:=xlPasteFormats
    newWorkbook.Worksheets(2).Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    newWorkbook.Worksheets(2).Name = "XL FILE"
 
    Application.CutCopyMode = False

    newWorkbook.SaveAs newWbPath
    newWorkbook.Close
  
  


End Sub

With the second Worksheet, I get the "out of range" error

Thanks in advance,

David

 

[DavyJones90]

I added this to my sheets and now it does add a sheet, but is also ads an empty sheet called "Sheet2" how do I get rid of it?

newWorkbook.Sheets.Add After:=Sheets("Values")
    newWorkbook.Sheets.Add.Name = "XL FILE"

 

[Fluff]

Just remove the first of those two lines.

 

[DavyJones90]

Just remove the first of those two lines.

Thanks, worked but had to add newWorkbook.Sheets.("XL FILE") After:=Sheets("Values") so it would sort correctly

 

[Fluff]

Glad you sorted it & thanks for the feedback.