save only selection and subtotal

  • Thread starter Thread starter Filips Benoit
  • Start date Start date
F

Filips Benoit

Hey,

My sheet has 1000 row with payments of clients.
In row 1 i have autofilter to select 1 client
In row 1001 i have the subtotal-function showing the total for this client.

I should print and email the clients data
The print only show his data, no problem.
But the email shows his data but contains all data !!!

How can i save only the clients data to a new workbook before emailing it to
the client ??

Thanks,

Filip
 
I don't know if you want a macro or to do it manually. You can manually copy
the filter rows by going to worksheet menu

Select the filtered visible rows
Edit - Go To - Special - Visible

Then copy the worksheet to a new workbook and mail like you did before.
 
Hey,

My sheet has 1000 row with payments of clients.
In row 1 i have autofilter to select 1 client
In row 1001 i have the subtotal-function showing the total for this client.

I should print and email the clients data
The print only show his data, no problem.
But the email shows his data but contains all data !!!

How can i save only the clients data to a new workbook before emailing it to
the client ??

Thanks,

Filip
Click to expand...

Try pasting row 1001 (sub total) into a new sheet when you do paste
make sure is a "Paste Special" on the edit and link it to the main
sheet.

Johnnyboy
 
Not as neat and concise as it could be but does the job.

Operates on client names in column A

Sub create_wkbook()
Dim myval As String
myval = InputBox("Type a Client's Name")
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=myval
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\program files\microsoft office\exceldata\" _
& myval & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
Selection.AutoFilter
Range("A1").Select
End Sub


Gord Dibben MS Excel MVP
 
I modified one of rond De Bruin's macros.


Sub Mail_Workbook()
' Works with Excel 97-2007.
Dim wb As Workbook

Set wb = Workbooks.Add(template:=xlWBATWorksheet)

With ThisWorkbook.ActiveSheet
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & Lastrow).SpecialCells(Type:=xlCellTypeVisible).Copy _
Destination:=wb.Sheets(1).Rows(1)
End With


' Check to see if this is Excel 2007 and is not a macro-enabled file.
If Val(Application.Version) >= 12 Then
' The code 51 represents the enumeration for a macro-free Excel 2007
Workbook (.xlsx).
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file that will be removed if
you try to send this file." & vbNewLine & _
"Save the file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If
 
Finaly it becaume this and works ok, thanks

Set Destwb = Workbooks.Add(template:=xlWBATWorksheet)

With ThisWorkbook.ActiveSheet
Lastrow = .Range("J" & Rows.Count).End(xlUp).Row
.Rows("2:" & Lastrow).SpecialCells(Type:=xlCellTypeVisible).Copy
Destination:=Destwb.Sheets(1).Rows(1)
End With

Filip
 
Back
Top