Copy a workbook with all sheets except formulas??? 1

[MrBullitt]

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?

 

[combo]

I'd make a copy and overwrite formulas by values:

Dim wb As Workbook, ws As Worksheet
ThisWorkbook.Sheets.Copy
Set wb = Workbooks(Workbooks.Count)
For Each ws In wb.Worksheets
    ws.UsedRange.Copy
    ws.UsedRange.PasteSpecial Paste:=xlPasteValues
Next ws
Application.CutCopyMode = False

combo

 

[MrBullitt]

Thanks for the input.


It does copy and create and new workbook - however - the values are missing and the cells just show #VALUE! - what went wrong here? :-(

 

[combo]

Have the formats been copied?
Any special formulas, formats or values in source cells that give #VALUE!? Does it happen in all cells?
The code works with the workbook where the code is stored (ThisWorkbook reference), is it what you need?

combo

 

[SkipVought]

Make sure that Application.Calculation is assigned MANUAL before the COPY process.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MrBullitt]

Combo:

yes every cell which was supposed to have a number in it just displays #VALUE!

formats, formatting, background colour have been copied.


this is last piece of the puzzle...

 

[combo]

Make sure that your source workbook is not a source of the error. If the calculation is set to manual, recalculate it. Do you still have proper values?

It was not discussed, but I assumed that in the view of the window 'formulas' box is unchecked and you can see only calculation results.

Replicate the code manually:
1) select all sheets and copy them, set destination to new workbook,
2) select sheet in newly created workbook, select all cells, copy & paste values.
Do you get #VALUE! in any of the above steps?




combo

 

[MrBullitt]

The issue has been resolved. Thanks to all inputs I got here!

 

[MrBullitt]

The problem was solved by putting "Application.Calculation = xlCalculationManual" as the very first line of code and the corresponding for setting back to automatic as the very last line of code.

It was waiting for values to load and this apparently copied the not-yet filled cells.


cheers and thanks guys!!!!