Copy Excel workbook without formulas??

[Webmonkey]

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:

[VBA]
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
[/VBA]


Can you guys help me with the loop?

[GTO]

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]

[vba]

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
[/vba]

[Webmonkey]

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]

Take another backup

[Webmonkey]

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]

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]

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.


[VBA]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[/VBA]

[Bob Phillips]

Have you got the calculation mode set to manual?

[Webmonkey]

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]

Try embedding

[vba]
Application.CalculateFull
[/vba]

before the For Each

[Webmonkey]

Try embedding

[vba]
Application.CalculateFull
[/vba]
before the For Each

no effect at all :-(

[Webmonkey]

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]

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]

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]

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:

[vba]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[/vba]
And it works... strange... - could it have been something about indentation of code?? I am used to closing functions with "}" :-)

[Webmonkey]

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]

ok.

i needed to set the [VBA]Application.Calculation = xlCalculationManual[/VBA]

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]

Remember what I asked you in #9