Try this Also..
Option Explicit
Sub Mtest()
On Error GoTo Err:
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim sPath As String, sFname As String
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Location
to save"
Application.FileDialog(msoFileDialogFolderPicker).Show
sPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) &
"\"
sFname = "Statement as on " & Format(Date, "mm-dd-yyyy")
With ActiveWorkbook.ActiveSheet
Sheets(Array(.Name, .Previous.Name)).Select
Sheets(Array(.Name, .Previous.Name)).Copy
End With
ActiveWorkbook.SaveAs (sPath & sFname)
ActiveWorkbook.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Err:
If Err.Number <> 0 Then
MsgBox "Do Not select First Sheet", vbInformation
End If
End Sub
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Mahesh parab
Sent: Wednesday, August 31, 2011 6:24 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ ***VBA code to copy and save as new book***
Hi Prabhu
Try
Option Explicit
Sub Mtest()
Dim sPath As String, sFname As String
sPath = ThisWorkbook.Path & "\"
sFname = "Statement as on " & Format(Date, "mm-dd-yyyy")
With ThisWorkbook
Sheets(Array(ActiveSheet.Name, ActiveSheet.Previous.Name)).Select
Sheets(Array(ActiveSheet.Name, ActiveSheet.Previous.Name)).Copy
End With
ActiveWorkbook.SaveAs (sPath & sFname)
ActiveWorkbook.Close True
Application.DisplayAlerts = False
End Sub
Thanks
Mahesh
On Wed, Aug 31, 2011 at 5:57 PM, Prabhu <prabhugate...@gmail.com> wrote:
Hi Friends,
I need VBA code for copy existing sheet(s) in to a new workbook.
i.e. I needs to copy two sheets (Active sheet and previous to the active
sheet)from a workbook and the same two sheet needs to same as a new work
book In a specified location with specified name(example like "Statement as
on 31stAugust 2011(current date)"
Note:Sheet name may varies.I need to copy active and previous to the active
sheet
Plz any one can help me to get the same.
Regards,
Prabhu
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
