Printing Problems

S

Subs

Hi

I have a printing problem. I Have a workbook that has many worksheeets,
each containing data of different sizes.
I want users to be able to choose the Dates i.e startDate and stopDate
which run along row 6 on every worsheet.
Also the code finds the end row of every sheet.
The code I have written works the first time round a loop but when it gets
to the next sheet it prints the whole sheet not just the data selected by
the user.
Here is the code I really would be grateful for any help on this macro.

Sub PrintBigBook()

Dim startDate As String
Dim stopDate As String
Dim startCol As Integer
Dim stopCol As Integer
Dim rowNum As Range
Dim lastRow As Long
Dim Ws As Worksheet


Sheets("Management Summary").Activate

startDate = InputBox("Enter the Beginning Date: (dd/mm/yy)")
If startDate = "" Then End
stopDate = InputBox("Enter the End Date: (dd/mm/yy)")
If stopDate = "" Then End

For Each Ws In Worksheets
startCol = Ws.Rows(6).Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Column
stopCol = Ws.Rows(6).Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Column

Set rowNum = Ws.Range("A1").SpecialCells(xlCellTypeLastCell)
lastRow = rowNum.Row

On Error Resume Next
Ws.Names.Add "Print_Area", Ws.Range(Cells(6, startCol), Cells(lastRow,
stopCol))
Ws.PrintOut Copies:=1

Next Ws

End Sub
 
D

Don Guillett

I think you will have to set your print area by modifying the usual
ws.PageSetup.PrintArea = "$A$1:$C$3"
to something like
ws.PageSetup.PrintArea = _
Ws.Range(Cells(6, startCol), Cells(lastRow,stopCol)).address
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top