vba to copy all sheets of open workbook to a new sheet

[JEAN1972]

Hi All

I have a workbook named AAA with several sheets named page 1 page 2 and so on.

Target: to copy all sheets on that workbook AAA,create a new book and paste all the sheets with same sheet tab name of AAA.

However the macro should be activated from worksheet AAA to do the task but do not save the new workbook as I will do it manually.


Thanks if anyone can assist

[xlnitwit]

Hi,

All you need do is copy an array of sheets

Sub copySheets()
thisworkbook.sheets(array("Page 1", "Page 2", "Page 3")).Copy
End Sub

Edit: I missed that you wanted all the sheets- AlphaFrog has given you the simplest solution for that.

[AlphaFrog]

Sub Copy_Sheets_To_New_Workbook()
    ThisWorkbook.Sheets.Copy
End Sub

[JEAN1972]

Hi xlnitwit & AlphaFrog

Thank you for both solution works a charm