Copying worksheet to another workbook

[Haider Quazilbash]

Hello All

I have a workbook A with around 20 worksheets in it. I wish to copy 3
specific worksheets to a new workbook B.

I have a written the following VBA code to perform the operation:

Private Sub cmdSaveData_Click()
Dim FN As String 'get user to select a file
FN = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls),
*.xls")
If FN = "False" Then
MsgBox "File Save Cancelled by User"
Else
Sheets("DataX").Select
Sheets("DataX").Copy
ActiveWorkbook.SaveAs Filename:=FN, _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
End If
End Sub

Problem: The worksheet being copied has VBA codes associated with it which
I do not want copied to the new workbook. How can I copy just the sheet
without the VBA code & links; i.e. copy only the VALUES and FORMATTING

Any help is greatly appreciated.

Haider

 

[steve]

Haider,

Start with this and adapt it to your code...
[watch for word wrap]

Sheets("DataX").Cells.Copy
Workbooks.Add
Cells(1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Cells(1, 1).PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

You might want to use Workbooks().Sheets().Range() references to more
easily go between the 2 books.

 

[Tom Ogilvy]

Private Sub cmdSaveData_Click()
Dim FN As String 'get user to select a file
FN = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls),
*.xls")
If FN = "False" Then
MsgBox "File Save Cancelled by User"
Else
' Sheets("DataX").Select
Sheets("DataX").Copy
ActiveSheet.UsedRange.Formula = Activesheet.UsedRange.Value
ActiveWorkbook.SaveAs Filename:=FN, _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
End If
End Sub

To get rid of the code, you would need to look a Chip Pearson's site

http://www.cpearson.com/excel/vbe.htm

for the basics.