Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Copy Worksheet To New Workbook & Save

  1. #1
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Copy Worksheet To New Workbook & Save

    I have a workbook with a worksheet named "Report"... this sheet and it's macros generate a report from data on another sheet...

    I'm adding a button to copy the (filled) Report sheet to a new workbook, rename the new sheet in the new workbook "Rep[Name]", then save and close the new workbook...

    I've recorded a maco of me doing these tasks manually, but what's recorded seems very generic, and does not seem to specify what's really occurring...
    For example (in part);
    > Rightclick on the Report tab
    > Move or Copy
    > To Book: (new book)
    > [x]Create a Copy
    > [OK]

    yields:
    VB:
    Sub Macro1() 
         '
         ' Macro1 Macro
         ' Macro recorded 10/17/2006 by Bryan
         '
        Sheets("Report").Select 
        Sheets("Report").Copy 
    End Sub 
    
    
    This recorded macro does not specify (new book), or Before:"Sheet1" as was the result of my actions... and when replayed back, only makes a copy in the active workbook, before the active sheet...

    Please offer suggestions as to what the copy code should look like, as the recorder macro clearly is not it...

    TiA,
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

  2. #2
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Copy Worksheet To New Workbook & Save

    Bryan

    As far as I can see that code should create a new workbook containing only 1 sheet, Report.

    The Copy method has 2 optional arguments, Before and After.

    These determine where the worksheet(s) are copied to.

    If omitted a new workbook is created.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Re: Copy Worksheet To New Workbook & Save

    When a new workbook is created via .Copy, is the focus changed to that new workbook?, or does the new workbook then have to be .Selected?...
    If the latter, how do you refer to this new workbook of an unknown name?
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Copy Worksheet To New Workbook & Save

    Try this.
    VB:
    Dim wb As Workbook 
     
    Worksheets("Report").Copy 
    Set wb = ActiveWorkbook 
    wb.SaveAs "New Report.xls" 
    wb.Close 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    21st July 2006
    Location
    Florida, USA
    Posts
    186

    Re: Copy Worksheet To New Workbook & Save

    Thanks for the input...
    It's now working just fine, with the following code;
    VB:
     'Save Report
    If CheckBox2 = True Then 
         
        Dim DstFile As String 'Destination File Name
         
        Dim RN As String 'Rep Name
        RN = Range("A2").Value 
         
        Dim FD As String 'From date
        FD = Range("J1").Value 
         
        Dim TD As String 'To date
        TD = Range("J2").Value 
         
        Unload UserForm1 
         
         'Copy worksheet
        Application.ScreenUpdating = False 
        Dim wb As Workbook 
        Dim NewShtName As String 
        NewShtName = "Report for " & RN 
         
        Sheets("Reports").Copy 
        Set wb = ActiveWorkbook 
        wb.Sheets("Reports").Name = NewShtName 
         
         'Prompt for SaveAs name
        DstFile = Application.GetSaveAsFilename _ 
        (InitialFileName:=RN & "report" & FD & "to" & TD & ".xls", _ 
        Title:="Save As") 
        If DstFile = "False" Then 
            MsgBox "File not Saved, Actions Cancelled." 
            Exit Sub 
        Else 
            wb.SaveAs DstFile 'Save file
            wb.Close 'Close file
        End If 
        Workbooks("Job Log.xls").Activate 
        MsgBox ("File Saved") 
        Application.ScreenUpdating = True 
    End If 
    
    
    Bryan
    "Simplicity is the ultimate sophistication." Leonardo da Vinci (1452-1519)

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Save Worksheet As Values Only To New Workbook
    By Claudia Paalvast in forum EXCEL HELP
    Replies: 1
    Last Post: March 28th, 2008, 08:32
  2. Save Each Worksheet In Workbook As Text
    By kitvel in forum EXCEL HELP
    Replies: 2
    Last Post: November 21st, 2007, 16:09
  3. Save One Worksheet From Workbook
    By JoePineapples in forum EXCEL HELP
    Replies: 2
    Last Post: April 19th, 2007, 18:10
  4. Protect Worksheet & Save Workbook
    By ahmedwaseem2000 in forum EXCEL HELP
    Replies: 5
    Last Post: October 30th, 2006, 15:37
  5. Export/Save worksheet to another workbook
    By shayne-tas in forum EXCEL HELP
    Replies: 1
    Last Post: September 27th, 2005, 05:29

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno