save only selection and subtotal

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
 
J

Joel

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.
 
J

Johnnyboy5

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
 
G

Gord Dibben

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
 
J

Joel

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
 
F

Filips Benoit

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
 
Top