Set WSN = WBN.Worksheets(l)

1 Set up a title on WSN

WSN.Cells(1, 1).Value = "Report of Sales to " & WhichCust 1 Copy data from WSO to WSN

WSO.Cells(1, NextCol + 2).CurrentRegion.Copy Destination:=WSN.Cells(3, 1) TotalRow = WSN.Cells(65536, 1).End(xlUp).Row + 1 WSN.Cells(TotalRow, 1).Value = "Total" WSN.Cells(TotalRow, 2).FormulaR1C1 = "=SUM(R2C:R[-1]C)" WSN.Cells(TotalRow, 4).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

1 Format the new report with bold WSN.Cells(3, 1).Resize(1, 4).Font.Bold = True WSN.Cells(TotalRow, 1).Resize(1, 4).Font.Bold = True WSN.Cells(1, 1).Font.Size = 18

WBN.SaveAs "C:\" & WhichCust & ".xls" WBN.Close SaveChanges:=False


' clear the output range, etc. Range("J1:Z1").EntireColumn.Clear

End Sub

Figure 11.18

Immediately after the Advanced Filter, we have just the columns and records needed for the report.

Figure 11.19

After copying the filtered data to a new sheet and applying some format-ting,we have a good-looking report to send to each customer.

□ Microsoft Excel - CDE IN t.. xls

01= Edit

VIbvî Insert Fwmet

Tools Beta WindoJ

: J J J j J jj.


- A £

• îljj

0 0

Post a comment