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 total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,180
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,180
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,180
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,822
Messages
5,446,710
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top