Prints Sheets and SaveAS Macro

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
114
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 can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,211
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
114
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,665
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
114
I tried that. It didn't solve it.
 

DanteAmor

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

Forum statistics

Threads
1,089,692
Messages
5,409,839
Members
403,280
Latest member
sepanikm

This Week's Hot Topics

Top