PDA

View Full Version : Solved: Copy and paste worksheets to a new workbook, minus the formulas



jacksonworld
02-03-2008, 10:04 PM
Hi all,

I am trying to copy and paste all the worksheets from one workbook to a new workbook, minus the formulas.

I have some code below which works, but only if I have exactly 6 worksheets, with the names Sheet1, Sheet2, etc.

Ideally, I would like this to work irrespective of the number or names of the worksheets.

I would be greatful for any assistance. Thanks


Sub CopyWorksheets()
Dim thisWB As Workbook
Dim newWB As Workbook
Dim numSheets As Long

Set thisWB = ActiveWorkbook
numSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 6
Set newWB = Workbooks.Add

Call CopyData(thisWB, newWB, "Sheet1")
Call CopyData(thisWB, newWB, "Sheet2")
Call CopyData(thisWB, newWB, "Sheet3")
Call CopyData(thisWB, newWB, "Sheet4")
Call CopyData(thisWB, newWB, "Sheet5")
Call CopyData(thisWB, newWB, "Sheet6")

Application.SheetsInNewWorkbook = numSheets

newWB.SaveAs "New.xls"

Set newWB = Nothing
Set thisWB = Nothing
End Sub

herzberg
02-04-2008, 12:47 AM
The following will copy and paste whatever is in the source workbook into the target workbook, regardless of the number of worksheets. I haven't tested it out, though, so I'm not sure if it will work.
Sub CopyWorksheets()
Dim thisWB As Workbook
Dim newWB As Workbook
Dim numSheets As Long
Dim TargetSheet As Worksheet, SourceSheet As Worksheet

Set thisWB = ActiveWorkbook
numSheets = thisWB.Sheets.Count
Application.SheetsInNewWorkbook = numSheets
Set newWB = Workbooks.Add

'Variable is reused here
numSheets = 1

For Each SourceSheet In thisWB.Sheets
Set TargetSheet = newWB.Sheets(numSheets)
Call CopyData(thisWB, newWB, TargetSheet)
numSheets = numSheets + 1
Next SourceSheet

newWB.SaveAs "New.xls"

Set newWB = Nothing
Set thisWB = Nothing
Set TargetSheet = Nothing
Set SourceSheet = Nothing
End Sub As for pasting without formulae, you'll need to provide the code of the CopyData sub procedure. Without knowing what goes on in there, it's kinda difficult for me to visualize. Anyway, a general method would be:
Testrg.PasteSpecial xlPasteValues

Aussiebear
02-04-2008, 01:31 AM
Would it not be possible to change the active workbook format to show only the resulting values rather than the formulae and then copy the workbook to another workbook?

jacksonworld
02-04-2008, 03:37 PM
Thanks people. And sorry for not attaching the CopyData sub. It is below.

I tried herzberg's code, but received an error relating to the TargetSheet.

Aussiebear, I am open to all possibilities. Whatever is easiest.

Thanks again.



Private Sub CopyData(Source As Workbook, Target As Workbook, sh As String)
Static shIndex As Long
shIndex = shIndex + 1
Source.Worksheets(sh).Cells.Copy
With Target.Worksheets(shIndex)
.Cells.PasteSpecial Paste:=xlValues
.Cells.PasteSpecial Paste:=xlPasteFormats
.Name = sh
End With
End Sub

herzberg
02-04-2008, 06:42 PM
Regarding the error, change this: Call CopyData(thisWB, newWB, TargetSheet) to this: Call CopyData(thisWB, newWB, TargetSheet.Name) This will pass the target sheet's name to the CopyData procedure.

Try changing the procedure to this and see if it works:
Private Sub CopyData(Source As Workbook, Target As Workbook, sh As String)
Static shIndex As Long
shIndex = shIndex + 1
Source.Worksheets(sh).Cells.Copy
With Target.Worksheets(shIndex)
.Cells.PasteSpecial xlPasteValuesAndNumberFormats
.Name = sh
End With
End Sub

jacksonworld
02-04-2008, 07:03 PM
Thanks. I tried it, but there is now an error during the CopyData sub at:

Source.Worksheets(sh).Cells.Copy

herzberg
02-05-2008, 01:25 AM
My bad. The source sheet should be passed instead of the target sheet. So like, it should look like this:
Call CopyData(thisWB, newWB, SourceSheet.Name) Sorry about that.

jacksonworld
02-05-2008, 04:04 PM
Brilliant! That is fantastic.

Thanks for that. I appreciate it.