Prints Sheets and SaveAS Macro

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
115
I want to create a macro that Prints 3 sheets from my workbook and then launches the saveAs window

The 3 sheets I am looking to print are "Team 1" "Team 2" & "Team 3"

Is this possible to do in one macro since there are windows that will pop up for both printing and save as?
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,222
Office Version
2007
Platform
Windows
The 3 sheets are the ones that go to a new book so you can save it, if it's correct, try this:

Code:
Sub print_save()
  Dim shs
  shs = Array("Team1", "Team2", "Team3")
  Sheets(shs).PrintOut
  Sheets(shs).Copy
  With Application.FileDialog(msoFileDialogSaveAs)
      .Title = "Save as"
      .AllowMultiSelect = False
      .FilterIndex = 2
      If .Show Then
          ActiveWorkbook.SaveAs Filename:=.SelectedItems(1), FileFormat:=xlOpenXMLWorkbookMacroEnabled
          ActiveWorkbook.Close False
      End If
  End With
End Sub
 

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
115
Thanks so much Dante,
unfortunately I get a Script out of range error on the
Code:
 Sheets(shs).PrintOut
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,784
Office Version
2013
Platform
Windows
In the code, put space after Team, eg. Team 1, Team 2, Team 3.
 

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
115
I tried that. It didn't solve it.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,222
Office Version
2007
Platform
Windows
In this line the names of your sheets should go identically, check if there are spaces on the left or on the right

Code:
[COLOR=#333333]Array("Team 1", "      Team 2", "Team 3    ")[/COLOR]
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,222
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,956
Messages
5,447,556
Members
405,456
Latest member
melmelseh

This Week's Hot Topics

Top