Recalling a multiple sheet selection in VBA

catdaddy1

New Member
Joined
Aug 7, 2014
Messages
1
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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).
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,167
Messages
5,600,093
Members
414,361
Latest member
OJVIBES

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top