Recalling a multiple sheet selection in VBA

[catdaddy1]

Hi,

I have a macro that will select multiple sheets at one point. I'm trying to think of a way to recall that selection later on, possibly by naming it when it's first selected. I thought something like this would work, but no dice:

SheetstoPrint = Sheets(ActiveWindow.SelectedSheets)
.....
.....
SheetstoPrint.Select

Also tried replacing "Sheets" with "Array" and that didn't work either.


I might be approaching this totally wrong, so here's the relevant part of the macro. I read elsewhere that you can't print while a dialog box is showing, so I'm trying to move the line that deletes the dialog box up in front of the PrintOut line.


' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb

ActiveWorkook.SelectedSheets.PrintOut copies:=1
ActiveSheet.Select
End If
End If



' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete


Thanks,
catdaddy1

 

[Tyron]

Hi,

I have a macro that will select multiple sheets at one point. I'm trying to think of a way to recall that selection later on, possibly by naming it when it's first selected. I thought something like this would work, but no dice:

SheetstoPrint = Sheets(ActiveWindow.SelectedSheets)
.....
.....
SheetstoPrint.Select

Also tried replacing "Sheets" with "Array" and that didn't work either.


I might be approaching this totally wrong, so here's the relevant part of the macro. I read elsewhere that you can't print while a dialog box is showing, so I'm trying to move the line that deletes the dialog box up in front of the PrintOut line.


' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb

ActiveWorkook.SelectedSheets.PrintOut copies:=1
ActiveSheet.Select
End If
End If



' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete


Thanks,
catdaddy1

Hey catdaddy1,

Have you tried having the macro changing the value of a set of cells to be that of the sheet names? Then later recalling the values of those cells with a printing macro?

I am not very familiar with using VBA to print, but check out this web page and see if it gets you going in the right direction:

Sheets.PrintOut Method (Excel)

I currently am without printer as well so I am unable to test any code. Hopefully one of the other regulars can help.

later

Ty

 

[JoeMo]

Welcome to the board.

Here's a simple macro you can adapt to your code. To demonstrate it, first select several sheets in your workbook, then step through (using F8 key) the code below. Note that the object variable "shts" is dimensioned as an object NOT as a worksheet(s).

Sub SelectMultipleSheets()
Dim shts As Object
Set shts = ActiveWindow.SelectedSheets
Sheets(2).Select      '<-- Make this a sheet not in the shts group
shts.Select
End Sub