CODEWITHSUNDEEP
vbaexcelms-officeexcel-2010
RLH
RLH

Reputation: 15698

Using VBA, how can I select multiple rows, on multiple worksheets for printing?

I have a spreadsheet that contains multiple worksheets. I need to select all rows, in certain worksheets, so that I can print all rows, across all selected worksheets, to a single document.

The following code is used to select all pertinent worksheets and rows that are to be printed. This code works until I attempt to select rows from a second worksheet.

Dim no_selected_worksheets As Boolean
no_selected_worksheets = True

Dim list_choose As Long
For list_choose = 0 To FormsToPrint.ListCount - 1
    If FormsToPrint.Selected(list_choose) Then
        With Worksheets(list_choose + 2)
            If no_selected_worksheets Then
                .Select      'This works.
                .Rows.Select 'So does this.
                no_selected_worksheets = False
            Else
                .Select (False) 'This works too..
                .Rows.Select    ' but here, VBA reports the error:
                                ' "Select Method of Range class failed"
            End If
        End With
    End If
Next

How can I fix this bug?

Upvotes: 0

Views: 4931

Answers (2)

Tim Williams
Tim Williams

Reputation: 166136

Something like this:

Sub Tester()

Dim i As Long
Dim list_choose As Long

    For list_choose = 0 To FormsToPrint.ListCount - 1
        i = i + 1
        If FormsToPrint.Selected(list_choose) Then
            With Worksheets(list_choose + 2)
                .Select (i = 1)
                'EDIT below
                .PageSetup.PrintArea = .UsedRange.Address(false,false)
            End With
        End If
    Next

    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Upvotes: 1

Joe Mahley
Joe Mahley

Reputation: 1

I believe that the only way to use .select on a range is to have the worksheet "active". This should answer your question, but I think you've complicated things for yourself. 

.
.
Else
    .Activate       ' new line
    .Select (False) 
    .Rows.Select
End If
.
.

In Excel macros, I try to avoid using .select and instead refer to ranges directly. For instance if I want to copy values from one range to another it would be something like:

Worksheets(2).Range("A1").Value = Worksheets(1).Range("A1").Value

Also, looks like you're looping through the sheets by index number, starting at the second one until you get to ListCount. Be aware that the sheet index changes based on tab order. You may be better off looping through all worksheets in the workbook with a "For Each" loop and comparing each sheet's name to a white or black list.

Upvotes: 0

Related Questions