Prints Sheets and SaveAS Macro

[robgoldstein]

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?

 

[DanteAmor]

The 3 sheets are the ones that go to a new book so you can save it, if it's correct, try this:

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]

Thanks so much Dante,
unfortunately I get a Script out of range error on the

 Sheets(shs).PrintOut

 

[JLGWhiz]

In the code, put space after Team, eg. Team 1, Team 2, Team 3.

 

[robgoldstein]

I tried that. It didn't solve it.

 

[DanteAmor]

In this line the names of your sheets should go identically, check if there are spaces on the left or on the right

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

 

[robgoldstein]

I restarted and it is working perfect.

thank you so much for solving yet another issue.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.