Save range from one workbook to a new workbook

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

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 wrote:
>
> 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

Thanks Dave this works great!!


"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
>