File has grown to 300,000 KB on Disk
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
|