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

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:
Thanks,,,,, Your suggestion avoided numerous Lines of Code = Problems
WoW !! What a one-liner....
Tks
Jim

Final Code below (for anyone that's following this thread)

Sub CompressFile()
Dim ScrWB As Workbook
Dim DesWB As Workbook
Dim DesFileName As String
Dim DesFilePath As String
Application.ScreenUpdating = False
Set ScrWB = ThisWorkbook
DesFilePath = ScrWB.Path & "\"
DesFileName = ScrWB.Name
If Right(DesFileName, 4) = ".xls" Then
DesFileName = Left(DesFileName, Len(DesFileName) - 4)
End If
With Workbooks.Add
.SaveAs DesFilePath & DesFileName & "Compressed" & Format(Date,
"mmddyyyy")
End With
Set DesWB = ActiveWorkbook
ScrWB.Sheets.Copy after:=DesWB.Sheets(1)
Application.DisplayAlerts = False
DesWB.Sheets(1).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Ans = MsgBox("Do You Wish to Re-Save this Newly Compressed Workbook?",
vbYesNo)
If Ans = vbYes Then DesWB.Save
ScrWB.Close SaveChanges:=False
End Sub

"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

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

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

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

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

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

If you copy one sheet at a time, excel knows to link back to the only place the
values exist--in the original workbook.

If you copy multiple sheets at a time, excel knows that you want any links
between the copied sheets to point at the new workbook's sheets.

Why? 'Cause that's the way excel works. (Best I can do.)

Jim May wrote:

Dave: Sorry for the delay in asking this question, but why does this
DIFFERENCE exist when using the method:

1) Right-Click the sheetname tab, select Move or Copy... Check Create a copy,
New book (first time) and refer to same each succeeding time

Versus

2) Sheets.copy <<Code line you suggested

As #1 method INCORPORATES the Full-Path link into the formulas BUT #2 method
DOES NOT ???

Thanks again,,
Jim

"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

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: Sorry for the delay in asking this question, but why does this
DIFFERENCE exist when using the method:

1) Right-Click the sheetname tab, select Move or Copy... Check Create a copy,
New book (first time) and refer to same each succeeding time

Versus

2) Sheets.copy <<Code line you suggested

As #1 method INCORPORATES the Full-Path link into the formulas BUT #2 method
DOES NOT ???

Thanks again,,
Jim


"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

Sometimes things just are what they are;
Thanks again for your input.
Jim

"Dave Peterson" wrote:

If you copy one sheet at a time, excel knows to link back to the only place the
values exist--in the original workbook.

If you copy multiple sheets at a time, excel knows that you want any links
between the copied sheets to point at the new workbook's sheets.

Why? 'Cause that's the way excel works. (Best I can do.)

Jim May wrote:

Dave: Sorry for the delay in asking this question, but why does this
DIFFERENCE exist when using the method:

1) Right-Click the sheetname tab, select Move or Copy... Check Create a copy,
New book (first time) and refer to same each succeeding time

Versus

2) Sheets.copy <<Code line you suggested

As #1 method INCORPORATES the Full-Path link into the formulas BUT #2 method
DOES NOT ???

Thanks again,,
Jim

"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

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