VBA Script to have one print file instead of separate ones

**kolathon** · 02-01-2011, 11:54 AM

Hello All,

I currently have some code to pull information from a row on one sheet, transfer it into another and print that sheet. The trouble is I can have around 20 different lines which then gives me 20 different print requests. I want to be able to print it all in one request.

Sub PrintForms()
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim Msg As String
    Dim i As Integer
    
    Sheets("Form").Select
    StartRow = Range("StartRow")
    EndRow = Range("EndRow")
    
    i = 0
    
    If StartRow > EndRow Then
        Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
        MsgBox Msg, vbCritical, APPNAME
    End If
    
    For i = StartRow To EndRow
        Range("RowIndex") = i
        If Range("Preview") Then
            ActiveSheet.PrintPreview
        Else
            ActiveSheet.PrintOut
        End If
    Next i
End Sub

I know its the ActiveSheet bit that I'm struggling with, and I think I need to put the count of the sheets into an array and print the array. I'm just not sure how to do this within VBA code. Any help or pointers in the right direction would be much appreciated!

**protonLeah** · 02-02-2011, 01:55 AM

Unfortunately, the code you posted uses several named ranges which are meaningless without the workbook.

You said:

....pull information from a row on one sheet, transfer it into another and print that sheet....

but I'm not sure where in the code this is happening. What sheet are you copying from and what sheets are you pasting to (are the sheet names hard coded?)?

One suggestion is to use an array to hold the names of the sheets to be printed:
At the top of the module outside of any subs put:

Option Explicit
Option Base 1

You need the Option Base 1 because you are using a loop that counts from 1 and the array indices need to match the loop counter, otherwise they will start at zero.

ReDim PrintList(1 To (EndRow - StartRow)) As String

Then as you process your rows you add the names of the destination sheets to the array:

PrintList(RowNum) = DestinationSheetName

Once all the rows have been processed and the array holds the names of all the sheets to be printed, use:

Sheets(PrintList()).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1

**kolathon** · 02-02-2011, 04:39 AM

Thank you for your reply and apologies for the poor explanation! I currently have 2 sheets. One named "Form", One named "Data".

My data sheet I have around 15 separate columns with hold data about customer orders; name, address, etc.....

On the form sheet I can set which row of data that I want eg. row 1, 2, 3, 4 etc.. I have a column of 15 cells using the "indirect" function to pull the information from the data sheet into the form - The RowIndex in the code is the number of the row on the data sheet.

=INDIRECT("Data!a" &RowIndex)

The information in the column is then referenced inside a styled section which is the bit that's printed using a button with the macro on. At the moment, using the code posted before, the code cycles through the rows and each sheet is treated as a separate print job, so my aim is to try and get them all into one.

I hope I have made more sense this time........I'm not holding my breath though!!

Thread: VBA Script to have one print file instead of separate ones

Thread Tools

Display

VBA Script to have one print file instead of separate ones

Re: VBA Script to have one print file instead of separate ones

Re: VBA Script to have one print file instead of separate ones

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions