Printing Multiple Worksheets

[Cynops]

Hi!

I am working through two worksheets.
sheet one is a simple form of which only one cell is changed and unique, which I want to print.
sheet two is a huge list of data of which does included the one unique reference which relates to sheet one.

I will be adding 50-100 rows to sheet two weekly, thus giving me 50-100 different forms (Sheet one)

Is there a way to automatically do this? as I am currently copying and pasting 50-100 times and printing?

Sorry I am probably making this sound a lot more complicated than it actually is?

#PASSWORD: bridal

[bulina2k]

Hi there!
Make a button somewhere on Sheet "REPORT " and put this code under:

Sub print_selected()
Dim sel As Range
    If Selection.Columns.Count > 1 Then Exit Sub
    If Selection.Column <> "A" Then Exit Sub
    If Selection = "" Then Exit Sub
    If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
        For Each sel In Selection
            Sheets("FORM ").Range("C7").Value = sel.Value
            Sheets("FORM ").PrintOut
        Next
    End If
End Sub

The code works like this: because there is no criteria of sort, you'll have to select one or multiple items from column A in sheet REPORT and press the newly created button. Each item from selection will replace the ID in sheet FORM cell C7 and will print the form.

[Greg M]

Hi there,

See if the attached workbook does what you need. It uses the following code:

Option Explicit


Sub PrintWorksheets()

    Const sNUMBER_COLUMN    As String = "A"
    Const sSHEET_REPORT     As String = "REPORT "
    Const sNUMBER_CELL      As String = "C7"
    Const sWEEK_COLUMN      As String = "L"
    Const sSHEET_FORM       As String = "FORM "
    Const sWEEK_CELL        As String = "C18"

    Dim iNoOfReports        As Integer
    Dim rNumberCells        As Range
    Dim rNumberCell         As Range
    Dim rWeekCells          As Range
    Dim rWeekCell           As Range
    Dim wksReport           As Worksheet
    Dim wksForm             As Worksheet
    Dim vWeekNo             As Variant
    Dim iWeekNo             As Integer
    Dim rCell               As Range

    Set wksReport = ThisWorkbook.Worksheets(sSHEET_REPORT)
    Set wksForm = ThisWorkbook.Worksheets(sSHEET_FORM)

    With wksReport

        Set rNumberCells = Intersect(.UsedRange, _
                                     .Columns(sNUMBER_COLUMN))

        Set rWeekCells = Intersect(.UsedRange, _
                                   .Columns(sWEEK_COLUMN))

    End With

    Set rNumberCell = wksForm.Range(sNUMBER_CELL)
    Set rWeekCell = wksForm.Range(sWEEK_CELL)

    vWeekNo = InputBox("Specify the Week No", "Week Number")

    If vWeekNo <> vbNullString Then

        If IsNumeric(vWeekNo) Then

            iWeekNo = CInt(vWeekNo)

            If iWeekNo > 0 Then

                  iNoOfReports = 0

                  For Each rCell In rWeekCells.Cells

                      If rCell.Value = iWeekNo Then

                          rWeekCell.Value = iWeekNo

                          rNumberCell.Value = Intersect(rCell.EntireRow, _
                                                        rNumberCells).Value

                          wksForm.PrintOut

                          iNoOfReports = iNoOfReports + 1

                      End If

                  Next rCell

                  If iNoOfReports > 0 Then

                        MsgBox iNoOfReports & " reports printed for Week No " & _
                               iWeekNo, vbInformation, "Reports printed"

                  Else: MsgBox "No records located for Week No " & iWeekNo, _
                                vbInformation, "No records located"

                  End If        '   If iNoOfReports > 0

            Else: MsgBox "Invalid Week No", vbExclamation

            End If              '   If iWeekNo > 0

        Else: MsgBox "You must specify a numeric value", vbCritical, "Invalid input"

        End If                  '   If IsNumeric(vWeekNo)

    End If                      '   If vWeekNo <> vbNullString

End Sub

The highlighted values can be changed to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Cynops]

This works perfectly!

Thank you very much!

[bulina2k]

OK, new version with week-number sorting. I have added another button for week criteria. The selection based print remains the same.

Sub filter_week()
Dim weeknumber As Integer
Dim idcol As Range

weeknumber = InputBox("Week number to be printed")

With ActiveSheet
    Set idcol = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
    idcol.Resize(, 22).AutoFilter Field:=12, Criteria1:=weeknumber

idcol.SpecialCells(xlCellTypeVisible).Select
Call print_selected
If ActiveSheet.AutoFilterMode Then ActiveSheet.UsedRange.AutoFilter

End Sub