Hi Prabhu it would be great if you send workbook which u r using & path of workbook. that code is working fine for me.
Thanks Mahesh On Fri, Sep 2, 2011 at 12:33 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote: > *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 > -- ---------------------------------------------------------------------------------- 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
