PDA

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?

GTO
01-12-2011, 02:40 AM
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

GTO
01-12-2011, 04:00 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???:(

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 :)