Compatible with Office 97

Can anyone tell me why this Sub sends the Whole Workbook in Excel 97,
what I want to achieve is to send only the "Usage" sheet in all
versions of Excel.

Thanks

Pete


Private Sub MailUsageSheet()
Dim rng As Range
Dim wb As Workbook
Dim Arr() As String
Dim N As Integer
Dim cell As Range
Dim strdate As String

strdate = Format(Sheets("Usage").Range("B6"), "ddd dd mmm yyyy")
Set rng =
Sheets("DropDownLists").Columns("Q").Cells.SpecialCells(xlCellTypeConstants)
ReDim Preserve Arr(1 To rng.Cells.Count)
N = 0
For Each cell In rng
If cell.EntireRow.Hidden = False And cell.Value Like "*@*" Then
N = N + 1
Arr(N) = cell.Value
End If
Next cell
ReDim Preserve Arr(1 To N)
Sheets("Usage").Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False
ActiveWindow.Zoom = 75
Set wb = ActiveWorkbook
With wb
.SaveAs "Sack Usage.xls"
On Error Resume Next
.SendMail Arr, Subject:="Sack Usage " & strdate
On Error GoTo 0
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Sub

Hello Pete,

Straight from the Excel '97 help file...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

It should send a workbook containing the single sheet Usage.

--
Regards,
Tom Ogilvy
"Pete" <[email protected]> wrote in message
news:[email protected]...
> Can anyone tell me why this Sub sends the Whole Workbook in Excel 97,
> what I want to achieve is to send only the "Usage" sheet in all
> versions of Excel.
>
> Thanks
>
> Pete
>
>
> Private Sub MailUsageSheet()
> Dim rng As Range
> Dim wb As Workbook
> Dim Arr() As String
> Dim N As Integer
> Dim cell As Range
> Dim strdate As String
>
> strdate = Format(Sheets("Usage").Range("B6"), "ddd dd mmm yyyy")
> Set rng =
>
Sheets("DropDownLists").Columns("Q").Cells.SpecialCells(xlCellTypeConstants)
> ReDim Preserve Arr(1 To rng.Cells.Count)
> N = 0
> For Each cell In rng
> If cell.EntireRow.Hidden = False And cell.Value Like "*@*" Then
> N = N + 1
> Arr(N) = cell.Value
> End If
> Next cell
> ReDim Preserve Arr(1 To N)
> Sheets("Usage").Copy
> Cells.Copy
> Cells.PasteSpecial xlPasteValues
> Cells(1).Select
> Application.CutCopyMode = False
> ActiveWindow.Zoom = 75
> Set wb = ActiveWorkbook
> With wb
> .SaveAs "Sack Usage.xls"
> On Error Resume Next
> .SendMail Arr, Subject:="Sack Usage " & strdate
> On Error GoTo 0
> .ChangeFileAccess xlReadOnly
> Kill .FullName
> .Close False
> End With
> End Sub
>

Hi Tom,

When I Test this out at home on Excel 2003, I get at you describe, but
when sent from a PC with Office 97 on it at Work, I get an exact
replica of the workbook it is sending the "Usage" sheet from, I can't
understand why.

Pete

Sheets("Usage").Copy

Would create a single sheet workbook with the worksheet "Usage" in both
xl97 and xl2003. That workbook would be the activeworkbook in both. So I
don't see any obvious cause for the observed difference.

--
Regards,
Tom Ogilvy



"Pete" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom,
>
> When I Test this out at home on Excel 2003, I get at you describe, but
> when sent from a PC with Office 97 on it at Work, I get an exact
> replica of the workbook it is sending the "Usage" sheet from, I can't
> understand why.
>
> Pete
>

thanks, Tom. I'm back at Work tomorrow, so will observe more closely,
and may post back.

Pete