Split 5-sheet workbook so only 2 sheets are saved into 1 new workbook

[Reiper79]

My workbook has 5 sheets, but I want only two sheets saved to one new workbook.

Can someone please help me create a macro within my workbook so it runs when I click a button I use for another macro?

 

[mrshl9898]

You just need to copy the sheets

Sub CopyOver()
    Sheets(Array("Sheet3", "Sheet4")).Copy
    ActiveWorkbook.SaveAs Filename:="C:\Users\andrew.marshall\Desktop\test.xlsx"
End Sub

 

[Reiper79]

Excellent. Thanks.

What if I want the new workbook to automatically save to the same location as the original but named using: value of a cell_Assess - Driving_value of a cell ?

 

[mrshl9898]

I'm not sure what you mean with the cells sorry.

For the Path though:

Sub CopyOver()
    myPath = Application.ActiveWorkbook.Path
    Sheets(Array("Sheet3", "Sheet4")).Copy
    ActiveWorkbook.SaveAs Filename:=myPath & "test.xlsx"
End Sub

 

[Reiper79]

Thanks very much.

The first cell I'm referring to is the Driver's Name field. The second is the Assessment Date field.

Is that possible to do?

 

[mrshl9898]

Yes, something like this i'd suppose

 myPath & "\" & Range("A1") & Range("A2") & ".xlsx"

 

[Reiper79]

Thanks for all your help.