how to copy and save sheets in another new workbook.

[Nitesh Khot]

Hi.....

I my excel workbook....i have total 106 sheets...I want to copy each individual sheet in new workbook with same sheet name to workbook name..........is this possible to copy all sheets individual at one time using VBA.


Thanks
Nitesh khot

 

[Deepak]

This will do the same....

Sub Sheet_as_Wb()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
  ws.Copy
  ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
  ActiveWindow.Close False
Next
Application.ScreenUpdating = True
End Sub

 

[Nitesh Khot]

where this code put.....Thisworkbook or sheet.

I have entered code in thisworkbook,,but while executing getting error RUNTIME ERROR 13 TYPE MISMATCH

 

[Nitesh Khot]

yes.....got it....done...i have made mistake in declaration.......its done.....thanks alot.