View Full Version : Copy Excel workbook without formulas??
Webmonkey
01-12-2011, 02:24 AM
Hi guys!
I am trying to make my very first Excel macro and I have hit a snag. My background is Java, and this my first venture into anything VB or VB like...
I have a workbook with multiple sheets. I need to copy it all to a new workbook with exception of the excel formulas - just the values, number formatting, background colours etc.
The code I have so far does that, but only for the active sheet - can you help me out with looping through the rest?
So far I have the following code working:
Sub test()
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.Dialogs(xlDialogSaveAs).Show
End Sub
Can you guys help me with the loop?
Greetings,
Rather than copying/pastespecial, you might want to try something like:
Option Explicit
Sub exa()
Dim wbNew As Workbook
Dim wks As Worksheet
Set wbNew = Workbooks.Add(xlWBATWorksheet)
ThisWorkbook.Worksheets.Copy After:=wbNew.Worksheets(1)
Application.DisplayAlerts = False
wbNew.Worksheets(1).Delete
Application.DisplayAlerts = True
For Each wks In wbNew.Worksheets
wks.UsedRange.Value = wks.UsedRange.Value
Next
End Sub
Hope that helps,
Mark
Bob Phillips
01-12-2011, 02:41 AM
Sub test()
Dim wb As Workbook
Dim ws As Worksheet
Dim vecWS As Variant
Dim i As Long
Set wb = ActiveWorkbook
ReDim vecWS(1 To wb.Worksheets.Count)
For i = 1 To wb.Worksheets.Count
vecWS(i) = wb.Worksheets(i).Name
Next i
wb.Worksheets(vecWS).Copy
For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
Application.Dialogs(xlDialogSaveAs).Show
End Sub
Webmonkey
01-12-2011, 02:51 AM
wow - quick replies! Thanks!
I will test this out - only way for me to learn this type of code :-)
I hope none of these overwrite the sheet it is executed on :-)
(my first venture stripped all from the original - lucky I had a backup :-) )
Bob Phillips
01-12-2011, 02:59 AM
Take another backup :)
Webmonkey
01-12-2011, 03:22 AM
Hi!
This seem to do the loop with end save OK, but all cell which before had numbers, now screem "#VALUE!" at me.
What went wrong???:(
Greetings,
Rather than copying/pastespecial, you might want to try something like:
Option Explicit
Sub exa()
Dim wbNew As Workbook
Dim wks As Worksheet
Set wbNew = Workbooks.Add(xlWBATWorksheet)
ThisWorkbook.Worksheets.Copy After:=wbNew.Worksheets(1)
Application.DisplayAlerts = False
wbNew.Worksheets(1).Delete
Application.DisplayAlerts = True
For Each wks In wbNew.Worksheets
wks.UsedRange.Value = wks.UsedRange.Value
Next
End Sub
Hope that helps,
Mark
Hi!
This seem to do the loop with end save OK, but all cell which before had numbers, now screem "#VALUE!" at me.
What went wrong???:(
Hi there,
I am probably overlooking something, but I don't see how this could be? Can you attach the wb?
Webmonkey
01-12-2011, 04:21 AM
Hi there,
I am probably overlooking something, but I don't see how this could be? Can you attach the wb?
I would prefer not to attach the workbook, as it contain personal financial information.
I attached the code again with my code in the loop, currently I commented out the pasting operations.
In this state, I just get a copy of the workbook with a sheet# name, with all formating, formulas preserved.
So the only step that remains is to strip the formulas, otherwise it does what I am trying do to.
Option Explicit
Sub exa()
Dim wbNew As Workbook
Dim wks As Worksheet
Set wbNew = Workbooks.Add(xlWBATWorksheet)
ThisWorkbook.Worksheets.Copy After:=wbNew.Worksheets(1)
Application.DisplayAlerts = False
wbNew.Worksheets(1).Delete
Application.DisplayAlerts = True
For Each wks In wbNew.Worksheets
Cells.Select
Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next
End Sub
Bob Phillips
01-12-2011, 05:57 AM
Have you got the calculation mode set to manual?
Webmonkey
01-12-2011, 06:13 AM
Have you got the calculation mode set to manual?
Ehm.. where can I check it? :-)
When I browser through the original workbook, values are calculated after a short delay due to some remote retrieval and references.
Bob Phillips
01-12-2011, 06:20 AM
Try embedding
Application.CalculateFull
before the For Each
Webmonkey
01-12-2011, 06:44 AM
Try embedding
Application.CalculateFull
before the For Each
no effect at all :-(
Webmonkey
01-12-2011, 06:55 AM
hmm, if I remove the contains of the For Each loop, i just end up with a copy of the original with formulas and all.
The funny thing is that for a brief moment when running the macro, it also shows "#VALUE!" in the cells.
But once finished, values and formulas return...
this led me to think; am I copying values at the time when it is showing ""VALUE!"???
Hi!
This seem to do the loop with end save OK, but all cell which before had numbers, now screem "#VALUE!" at me.
What went wrong???:(
Webmonkey
01-12-2011, 07:10 AM
Ok, problem has been solved!
It was like Error 65 - a missing "wks" after the "next" in end of loop :-)
Thanks for all of the input and help I got here!
btw.
As said this is my first week of anything VB - please recommend a good site or book with "beginning VBA" :-)
Bob Phillips
01-12-2011, 08:16 AM
Sorry, that was not the issue. Although it is better to explicitly state the loop element in the Next statement, it is not necessary, and it will not cause the code to fail.
Webmonkey
01-12-2011, 08:19 AM
Sorry, that was not the issue. Although it is better to explicitly state the loop element in the Next statement, it is not necessary, and it will not cause the code to fail.
Ok, this is really strange...
This is the code I have now:
Sub test()
Dim wbNew As Workbook
Dim wks As Worksheet
Set wbNew = Workbooks.Add(xlWBATWorksheet)
ThisWorkbook.Worksheets.Copy After:=wbNew.Worksheets(1)
Application.DisplayAlerts = False
wbNew.Worksheets(1).Delete
Application.DisplayAlerts = True
For Each wks In wbNew.Worksheets
wks.UsedRange.Value = wks.UsedRange.Value
Next wks
Application.Dialogs(xlDialogSaveAs).Show
End Sub
And it works... strange... - could it have been something about indentation of code?? I am used to closing functions with "}" :-)
Webmonkey
01-12-2011, 09:15 AM
OK, I geuss I spoke to soon...
After re-loading the source workbook, I now again have the #VALUE!
Could it be related to the Application.CalculateFull ??
just before the For Each it does not seem to have some effect...
Is there any switch to stop a sheet from re-calculating?
Webmonkey
01-12-2011, 09:42 AM
ok.
i needed to set the Application.Calculation = xlCalculationManual
At the first line of my code and the corresponding code to set calculation back to auto again once I am done.
I the problem with #VALUE! was that while waiting for the values to load, it copied the "empty" values and thus I ended up with #VALUE! in my cells.
now it work!
Thanks once again guys!
Bob Phillips
01-12-2011, 10:25 AM
Remember what I asked you in #9 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.