Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.Special Cells(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compatible with Office 97
It should send a workbook containing the single sheet Usage.
-- Regards, Tom Ogilvy "Pete" wrote in message ups.com... 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.Special Cells(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compatible with Office 97
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compatible with Office 97
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" wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compatible with Office 97
thanks, Tom. I'm back at Work tomorrow, so will observe more closely,
and may post back. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFICE XP COMPATIBLE WITH VISTA | Excel Discussion (Misc queries) | |||
Problem sharing spreadsheet between Office10 (Office XP) and Office 11 (Office 2003) | Excel Programming | |||
UDF 2000 compatible? | Excel Programming | |||
UDF 2000 compatible? | Excel Programming | |||
Excel 2003 not compatible with Office 2k Web Components -- HELP! | Excel Programming |