Save range from one workbook to a new workbook

[Adella]

I would like to save 3 different ranges to a new workbook in 3 different
sheets. This is what I have so far:

Private Sub CommandButton5_Click()

Dim RecoC2Print As Range
Set RecoC2Print = Worksheets("RecoC2").Range("A1.Y550")
RecoC2Print.Copy

Dim Myfile As Variant

Myfile = Sheet17.Cells(31, 6)
Set Myfile = Workbooks.Add
Myfile.SaveAs "Planfile.xls" 'ACTUALLY WOULD LIKE THIS TO BE THE PATH IN
MYFILE ABOVE
'Workbooks(Myfile).Activate Tried this didn't seems to work

'Application.SheetsInNewWorkbook = 3
'ThisWorkbook.Activate
'Sheet1.Activate
Worksheets.Add.Name = "RecoC2"
RecoC2.Range("A1:Y550").PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.SaveAs Myfile

'Sheets(Array(Sheet30, Sheet4, Sheet35)).PrintOut PrToFileName:=plan.xls

End Sub

I am having a problem getting the new workbook to be the active one.

Thanks in advance for any help.

Adella

 

[Dave Peterson]

I'm not sure that this will help, but it may give you a couple of ideas:

Option Explicit
Private Sub CommandButton5_Click()

Dim RecoC2Print(1 To 3) As Range
Dim myFile As Workbook
Dim myFileName As String
Dim iCtr As Long

With ThisWorkbook
Set RecoC2Print(1) = Worksheets("RecoC2").Range("A1:Y550")
Set RecoC2Print(2) = Worksheets("recoC3").Range("a12:y323")
Set RecoC2Print(3) = Worksheets("recoc99").Range("b3:c12")
End With

myFileName = Sheet17.Range("F31").Value

Set myFile = Workbooks.Add(1) 'single sheet
With myFile
.Worksheets.Add after:=myFile.Worksheets(1)
.Worksheets.Add after:=myFile.Worksheets(2)

.Worksheets(1).Name = "RecoC2"
.Worksheets(2).Name = "RecoC3"
.Worksheets(3).Name = "RecoC99"

For iCtr = 1 To 3
RecoC2Print(iCtr).Copy
.Worksheets(iCtr).Range("a1").PasteSpecial Paste:=xlPasteValues
Next iCtr

.SaveAs Filename:=myFileName

End With

End Sub

 

[Adella]

Thanks Dave this works great!!

I'm not sure that this will help, but it may give you a couple of ideas:

Option Explicit
Private Sub CommandButton5_Click()

Dim RecoC2Print(1 To 3) As Range
Dim myFile As Workbook
Dim myFileName As String
Dim iCtr As Long

With ThisWorkbook
Set RecoC2Print(1) = Worksheets("RecoC2").Range("A1:Y550")
Set RecoC2Print(2) = Worksheets("recoC3").Range("a12:y323")
Set RecoC2Print(3) = Worksheets("recoc99").Range("b3:c12")
End With

myFileName = Sheet17.Range("F31").Value

Set myFile = Workbooks.Add(1) 'single sheet
With myFile
.Worksheets.Add after:=myFile.Worksheets(1)
.Worksheets.Add after:=myFile.Worksheets(2)

.Worksheets(1).Name = "RecoC2"
.Worksheets(2).Name = "RecoC3"
.Worksheets(3).Name = "RecoC99"

For iCtr = 1 To 3
RecoC2Print(iCtr).Copy
.Worksheets(iCtr).Range("a1").PasteSpecial Paste:=xlPasteValues
Next iCtr

.SaveAs Filename:=myFileName

End With

End Sub