Here is a stab at the code you require. Hiding and unhiding rows is easy if you know how. I printed a worksheet with the macro recorder on so you can see how you could tailor the printing if you wish. I would set a shortcut key to run this macro.
Dim RowCrnt As Integer
Dim RowLast As Integer
' Note: This operates on the active worksheet
Application.ScreenUpdating = False
RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row
' Hide all rows with a used cell and column "A" empty
For RowCrnt = 1 To RowLast
If IsEmpty(Cells(RowCrnt, "A")) Then
Range(RowCrnt & ":" & RowCrnt).EntireRow.Hidden = True
' For the following statements, I switched on the macro recorder,
' printed a sheet with all the headers and footers I wanted,
' switched off the macro recorder and copied the code out of the
' saved macro.
' Consider: .CenterHeader = "Activities for Acme Inc"
' If you name the worksheets for the client, the following would
' give you a heading for the appropriate client:
' .CenterHeader = "Activities for " & ActiveSheet.Name
.PrintTitleRows = ""
.PrintTitleColumns = ""
ActiveSheet.PageSetup.PrintArea = ""
.LeftHeader = ""
.CenterHeader = "Activities for Acme Inc"
.RightHeader = ""
.LeftFooter = "&D"
.CenterFooter = "Page &P of &N"
.RightFooter = "Copyright Nadir Co."
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
' Unhide all rows
Cells.EntireRow.Hidden = False
Loading above code to a module
From within Excel:
Visual Basic Editor. Alternatively, click
Alt+F11. You will probably see the Project Explorer down the left, the Immediate window in the bottom with the bulk of the screen grey.
Module. The grey section will go white.
- Copy and paste my code into the now white section. The macro can now be run against any sheet.
- Switch to Excel.
Macros.... Alternatively, click
Alt+F8. A small Macro window will be displayed. You will only have one macro so it will be selected and the
Run button will be active.
You can run the macro by clicking
Enter but you might find the following more convenient.
- From the Macro window, select
Options. You will now see a small Macro Options window.
- Enter a letter (I always use q) into the little box and click
- Close the Macro Window.
You can now switch to the appropriate client's work sheet and click
Ctrl+q to run the macro.
Adjust the macro to your requirements
In the middle of my code is my idea of how you might set up a report with "Activities for Acme Inc" at the top and date, page number and copyright at the bottom.
Replace this code as follows:
- From Excel, select
Record New macro. You see a small Record Macro window.
- Make sure the text box under "Store macro in:" says "This Workbook".
OK. The window disappears. You may see a very small window with a square button labelled Stop recording. If this window is visible, ignore it for the moment.
- Print a client report with whatever headings, footers, margins, etc. you require.
- If you can see the small window, click it. If you cannot see it, select
- Switch to the Visual Basic Editor. There will be a new Module. Click on it in the Project Explorer Window.
- You will see
Sub Macro1() all the statements to print a client report the way you like and
- Copy those statements (not sub and end sub) and paste them over the top of the equivalent statements in my macro.
If you do not include anything like client name in your report headers and footers, your macro will be ready for use. I give one example in my code where I use the worksheet name the report header. Without knowing your exact requirements I cannot be more precise but I hope this gives you a start.