Thanks Dave.

Rob
"Dave Peterson" wrote in message
...
It copies all the sheets from a workbook to a new workbook.

If you want code to copy code, take a look at Chip Pearson's site:
http://cpearson.com/excel/vbe.aspx

RobN wrote:

Thanks Dave, that makes good sense!

I'll give it a whirl.

Can you explain briefly what this macro actually does? I noticed it did
reduce the size of one of my files considerably, albeit without the VBA.
ie
from 7+mb to 1+mb. So, does it compress, as the name of the code
suggests,
or just clean it up somehow? It seems to me from the little I can
understand of the code that all it does is copy all the worksheets to a
new
workbook, but is there some magic of which I'm unaware?

Unfortunately it doesn't deal with Modules or Forms, so if you have the
code
to add to that I'll have to resort to copying that over manually. I
guess
too that I'll need to assign the code to each button, etc., or is there
some
magic that'll take care of that?

BTW, it takes about 10 mins or more to do that task!

Rob

"Dave Peterson" wrote in message
...
This portion:

With Workbooks.Add
.SaveAs DesFileName & Format(Date, "mmddyyyy")
End With

saves the new workbook.

I would have specified the fileformat. In xl2003, I'd use:

With Workbooks.Add
.SaveAs DesFileName & Format(Date, "mmddyyyy") & ".xls",
_
fileformat:=xlworkbooknormal
End With

You should be able to modify this (use VBA's help for the correct
fileformat).

Check out Ron de Bruin's tips for using .SaveAs in xl2007.
http://www.rondebruin.nl/saveas.htm



RobN wrote:

Any chance this code can be modified so that it saves it with VBA
still
available (ie as .xlsm)?

Rob

"Dave Peterson" wrote in message
...
I don't see anything that pops out why that should happen.

In my (one-time, exhaustive(?)) testing, it didn't occur for me.

Jim May wrote:

One small "glitch" however..
When Macro Concludes to VBE - Object-Browser is active...???
hummmmmm,,,,
any comments/suggestions?

"Dave Peterson" wrote:

Don't copy the sheets one by one.

Copy all the sheets to the new workbook in one fell swoop.

I used something like this:

Sheets.Copy _
after:=Workbooks("book2.xls").Sheets(1)



Jim May wrote:

I am attempting to Reduce the File size of a given file
(present
size
above)
and
am this far in the process. I have one small problem with the
output
of
this code,

For example in the Big File (ScrWB) in a cell I have
=+Daily!B35
<< which
is Fine

I am getting in the Finalized Small File (DesWB) the same cell

=+'[2007 11 CompressMacro.xls]Daily'!B35

How Can I eliminate the +'[2007 11 CompressMacro.xls]
so that I get only the =+Daily!B35

Thanks In Advance for any assistance..

Sub CompressFile()
Dim ScrWB As Workbook
Dim DesWB As Workbook
Dim DesFileName As String
Dim ScrShCount As Integer
Application.ScreenUpdating = False
Set ScrWB = ActiveWorkbook
ScrShCount = ScrWB.Sheets.Count
DesFileName = ScrWB.Name
If Right(DesFileName, 4) = ".xls" Then
DesFileName = Left(DesFileName, Len(DesFileName) - 4)
End If
With Workbooks.Add
.SaveAs DesFileName & Format(Date, "mmddyyyy")
End With
Set DesWB = ActiveWorkbook
For CurSh = 1 To ScrShCount
ScrWB.Sheets(CurSh).Copy After:=DesWB.Sheets(CurSh)
Next CurSh
DesWB.Sheets(1).Activate
DesWB.Sheets(1).Delete
Application.ScreenUpdating = True
Ans = MsgBox("Do You Wish to ReSave of this Newly Compressed
Workbook?",
vbYesNo)
If Ans = vbYes Then
DesWB.Save
Else
Exit Sub
End If
End Sub

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

--

Dave Peterson