Saving a Worksheet/Workbook with VALUES ONLY

[CAT]

Hello everyone,
I am a newbie and using Excell 2007.

I am using a worksheet which contains formulae, functions and, hopefully at
some stage, some VBA Code that will colour format a number of columns.
This worksheet is updated daily with new data.

At the end of the day, I would like to save a copy of the Current Worksheet
(named after the current date, say Sept 1, Sept 2, etc.) in the current
Workbook (named after the current month, say September), but WITHOUT any
formulae, functions or Codes, retaining only the values and, possibly, the
coloured columns. The saved worksheets/workbooks will be archived in a
folder, will not be needing anymore calculations and will be accessed for
viewing only.

I would like to keep the current worksheet as the constant main working
sheet and have all the data in it erased (after the daily save) WITHOUT
DELETING any of my Formulae/Functions or Codes.
In other words, to start the day with the same worksheet, with blank cells,
keeping all my headers, worksheet title and notes, and most importantly, WITH
all my coding, ready to receive new data.

By the same token, when we arrive to the 30th/31st of the month, I would
like to save and archive the Monthly Workbook (with all the daily saves) in a
folder, open a new workbook (say October) with my still active Worksheet,
complete with all the formulae/functions etc.,

And start anew.

I hope this is clear, if a tad repetitive!

How do I do this? In the simplest way?

Thank you for reading and helping if you can.

Cat

 

[Bernard Liengme]

You said "hopefully at some stage, some VBA code" That stage has come!
I recorded a macro that selected all cells, converted all formulas to values
(note that functions are part of formulas, so =SUM(A1:A10) is a formula that
uses the SUM function)
Then it does a SaveAs
When you get into VBA you can use an InputBox to ask for the name to use in
the save as.

Here it is
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 02/10/2008 by Bernard V Liengme
'

'
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "C:\Documents and Settings\Owner\My Documents\Excel Problems"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Owner\My Documents\Excel
Problems\OnlyValues.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

So now you need to know waht to do with this. Copy if from this message
Open Excel with the production file active, use ALT+F11 to open the VB
Editor;
On the menu bar use Insert | Module and paste the subroutine tinto the code
area (the lager part of the window)
A visit to David McRitchie's site on "getting started" with VBA will help
http://www.mvps.org/dmcritchie/excel/getstarted.htm
And/or purchase Excel VBA for Beginners by Walkenbach
best wishes

 

[CAT]

Hi Bernard,

Whoa, that was quick!
I am blessed!

I shall put this into practice ASAP. A previous poster to another of my
thread pointed me in the same direction as you did, i.e. the dmcritchie
getting started link.

As for the book, guess what? I did buy Excell 2007 VBA Programming for
Dummies by John Walkenbach this summer (along with a few more Dummies -
Excell 07, functions, etc.); the VBA one raised a few chuckles, gave me a
head spinning throbbing headache and by chap. 5, I had to go back to page 1
to regroup.

As for the VBA Code I was referring to, I posted a long and convoluted query
(crystal clear to me, mind you) on the Programming Section a couple of days
ago, requesting some help in working out how to colour code a bunch of
columns according to some specific conditional formatting I had laid out in a
separate table.

I did get a reply which, I figured out, would not work (even with my very
limited grasp, I could see that), but got stopped at the first hurdle, not
knowing how to name a table, and worse even, where to right-click a sheet
tab: blushes all round
:-(

I KNOW NOW, a kind poster enlightened me on another thread!

Since then, my original posting is fast disappearing, page by page, in the
deep black hole of unanswered queries and I suppose I shall have to rewrite
it in a - hopefully - clearer and more concise manner - good practice for
(better) logical thinking I guess.

So thank you again Bernard for your help: Deep breath, and I shall go in at
the deep end with your Code and hope for the best ;-)

Just in case everything explodes... How do I erase/delete a code in the
"viewing window"?

Kind Regards,
Cat

 

[Bernard Liengme]

If things go wrong, do not just delete the code in the code area - Excel
will think the file still has VBA code.
In the project window (left panel), right click Module1 for your workbook
and select Delete. You will be asked if you want to save the code, say No

best wishes

 

[CAT]

Thank you Bernard, will do.
I have learned quite a few things today.

Thanks again for your time and your patience.
Cat