Pull info from one Active Workbook and Save in Another

[geebra]

Hi Guys,

I am looking to macro that can pull information from a Active workbook and save/create it in another workbook.

I have attached the file. I am looking to get A7:A20 and J6:J20

So basically I would like to add a button on the Gas Analysis worksheet. click it and automatically creates a new excel with those 2 ranges.

Thanks for your help!

[patel]

Sub a()
Sheets(1).Range("A7:A20").Copy Sheets(2).Range("A7")
Sheets(1).Range("J6:J20").Copy Sheets(2).Range("J6")
Sheets(2).Copy

End Sub

[geebra]

Can i save it by the name of the sheets?

Say if its Jun 2012, Jul 2012 ...are the sheet names and save it in a particular directory?

Thanks so much

[Tinbendr]

Create activex button on sheet, then double click on it and drop this code in.
Private Sub CommandButton1_Click()
Dim WBNew As Workbook
Dim WS As Worksheet
Set WS = ActiveSheet
Set WBNew = Workbooks.Add

WS.Range("A7:A20").Copy WBNew.Worksheets(1).Range("A7")
WS.Range("J6:J20").Copy WBNew.Worksheets(1).Range("J6")

End Sub

[patel]

Sub (a)
filename=Sheets(1).name
Sheets(1).Range("A7:A20").Copy Sheets(2).Range("A7")
Sheets(1).Range("J6:J20").Copy Sheets(2).Range("J6")
Sheets(2).Copy
ActiveWorkbook.SaveAs Filename:=filename
End Sub