Copy Sheet Values to new WB

[GregR]

I am having trouble with the code to copy the sheet values to a new
workbook where the copyfrom WB has multiple sheets. The number of
sheets may vary, but always more than one. TIA

 

[Vasant Nanavati]

Can you define "having trouble?"

If you identify the source worksheet correctly, there should be no problem.
Posting your code would help.

 

[Guest]

Here is some real generic code. Without more details of what you need to do
this is the best I can give you...

Sub CopySheets()
Dim wbkNew As Workbook
Dim wksToCopy As Worksheet
Dim wksPaste As Worksheet

Set wbkNew = Workbooks.Add
Set wksPaste = wbkNew.Worksheets.Add
Set wksToCopy = ThisWorkbook.Sheets("Sheet1")
wksToCopy.Cells.Copy
wksPaste.Cells.PasteSpecial xlPasteValues

Set wksPaste = wbkNew.Worksheets.Add
Set wksToCopy = ThisWorkbook.Sheets("Sheet2")
wksToCopy.Cells.Copy
wksPaste.Cells.PasteSpecial xlPasteValues
End Sub

 

[GregR]

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

 

[STEVE BELL]

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...

 

[GregR]

Jim, what I would like, is to have generic code in my personal.xls that
does the above, but also pastes the formatting. As I understand your
code, if this code is not in a specific workbook module, it copies the
sheets from my personal.xls, as it is "ThisWorkbook". Also, I don't
want it set to a specific number of worksheets, but however many there
are in the active workbook. TIA

Greg

 

[Vasant Nanavati]

Thanks for the compliment, Steve, but I did not provide Greg with the code!
<g>

However, the following might be more concise:

Sub Test()
Dim ws As Worksheet, c As Range
Application.ScreenUpdating = False
Workbooks("source.xls").Worksheets.Copy
For Each ws In Worksheets
For Each c In ws.UsedRange.Cells
c = c
Next
Next
Application.ScreenUpdating = True
End Sub

Regards,

Vasant

 

[Guest]

Here is some code... This will copy over everything including pictures,
shapes...

Sub CopySheets()
Dim wbkCurrent As Workbook
Dim wbkNew As Workbook
Dim wksToCopy As Worksheet
Dim wksPaste As Worksheet

Set wbkCurrent = ActiveWorkbook
For Each wksToCopy In wbkCurrent.Worksheets
If wbkNew Is Nothing Then
wksToCopy.Copy
Set wbkNew = ActiveWorkbook
Else
wksToCopy.Copy wbkNew.Sheets(wbkNew.Sheets.Count)
End If
Set wksPaste = wbkNew.ActiveSheet
wksToCopy.Cells.Copy
wksPaste.Cells.PasteSpecial xlValues
wbkCurrent.Activate
Next wksToCopy

End Sub

 

[GregR]

Vasant, Steve, and Jim thank you very much.

Greg