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

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

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

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