Announcement

Collapse
No announcement yet.

Copy Worksheet To New Workbook & Save

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Code:
    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
    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.
    Boo!

    Comment


    • #3
      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)

      Comment


      • #4
        Re: Copy Worksheet To New Workbook & Save

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

        Comment


        • #5


          Re: Copy Worksheet To New Workbook & Save

          Thanks for the input...
          It's now working just fine, with the following code;
          Code:
          '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)

          Comment

          Working...
          X