Copying a range of cells of active worksheet to a new workbook

[Niko]

Hi All,

I have one open workbook containing many sheets.
I want to copy a range of cells into a new workbook
I create a new workbook using following VBA code.

If I use following code it creates a new workbook with a copy of entire active worksheet.


Sub Copysheet()
Dim Fs As String
Fs = Application.GetSaveAsFilename(fileFilter:="xls Files (*.xls), *.xls")
If Fs = "False" Then Exit Sub
Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Fs
End Sub


However, I want to copy a range of cells ("A10:L100") only instead of entire active sheet. Hence, I wrote following code:


Sub Copysheet()
Dim Fs As String
Fs = Application.GetSaveAsFilename(fileFilter:="xls Files (*.xls), *.xls")
If Fs = "False" Then Exit Sub
Sheets("Sheet1").Range("A10:L100").Copy
ActiveWorkbook.SaveAs Fs
End Sub

But, it dosen't work. It just creates a new workbook with all sheets instead of only active sheet.

Can someone help me?

Thanks
Niko

[Jacob Hilderbrand]

Try this:



Option Explicit

Sub Copysheet()
Dim Fs As String
Dim Wkb As Workbook
Dim NewWkb As Workbook
Fs = Application.GetSaveAsFilename(fileFilter:="xls Files (*.xls), *.xls")
If Fs = "False" Then Exit Sub
Set Wkb = ActiveWorkbook
Set NewWkb = Workbooks.Add
Wkb.Sheets("Sheet1").Range("A10:L100").Copy _
Destination:=NewWkb.Sheets(1).Range("A1")
NewWkb.SaveAs Fs
End Sub

[Niko]

Hey DRJ!,

That's gr8.

It is working very fine.

Thanx.

Niko

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care