Greg,

Since you got the code from Vasant it should already be the best...

If I had to change anything it would be the variable names.
My preference is to keep them as short as possible, but it is only a
preference...

You might want to start the code with the below:

This one stops screen flashing and in some cases helps the code to run
faster:

Application.ScreenUpdating = False
' the code here
Applications.ScreenUpdating = True

You can add similar structures to turn event code off (if there is any),
And some turn calculation off and on if there are a lot of formulas.

Other wise - Vasant definitely knows how to write great code...
--
steveB

Remove "AYN" from email to respond
"GregR" wrote in message
oups.com...
Vasant, got it to work, but can the code be improved?

Sub CopySheetsValues()

Dim ThisBookSheets As Long
Dim OldNumSheets As Long
Dim i As Long
Dim ThisWorkbookName As String

OldNumSheets = Application.SheetsInNewWorkbook
ThisBookSheets = ThisWorkbook.Worksheets.Count
ThisWorkbookName = ThisWorkbook.Name

' Add new workbook with as many sheets as are in the current workbook
Application.SheetsInNewWorkbook = ThisBookSheets
Workbooks.Add


For i = 1 To ThisBookSheets
Workbooks(ThisWorkbookName).Sheets(i).Cells.Copy
With Sheets(i).Cells
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlFormats
End With
Next i

Application.SheetsInNewWorkbook = OldNumSheets

End Sub

Greg