Results 1 to 9 of 9
  1. #1
    BakerMan
    Guest

    Saving One Sheet into a separate workbook (2000)

    I have made a workbook using techniques that you guys supplied(thanks). It looks up and gives me a quick summary from a table of 800+names and info. After the summary sheet is made, I save the file according to the name selected and the date (with the macro).
    I was wondering how to just save the summary part of sheet one into a separate workbook. All of the data is in sheet 1 of the workbook. I need this for a couple of reason's - One reason is to reduce the file size of the individual sheet, the other is to be able to email the sheet without distributing confidential -personal data-to the internet.
    I have a sample attached.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving One Sheet into a separate workbook (2000)

    <pre>Option Explicit
    Sub SaveSummarySheet()
    Dim sFName As String
    ActiveSheet.Copy
    sFName = "C:InfoForms" & Range("B2") & _
    "-Summary" & Format(Date, "mmddyyyy")
    MsgBox "Summary Sheet will be saved as " & sFName
    ActiveWorkbook.SaveAs (sFName)
    End Sub</pre>


    If the summary sheet is not going to be the activesheet, you can be explicit in copying it, in your example you could use:
    <pre>Sheets("Sheet1").Copy</pre>


    Change the name as appropriate.

    Steve

  3. #3
    BakerMan
    Guest

    Re: Saving One Sheet into a separate workbook (2000)

    The code you supplied worked well.

    I didn't explain well though. Is their a way that I can save just the data that the 'Data Validation' and VLookup formulas generate. In other words I don't want to save the data table that is also in sheet one. I want to only save the info in the cells A1:E31 ---thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Saving One Sheet into a separate workbook (2000)

    How about this. I added code to delte the rows and columns outside the region.

    Steve
    <pre>Option Explicit
    Sub SaveSummarySheet()
    Dim sFName As String
    ActiveSheet.Copy
    Columns("F:IV").Delete
    Rows("32:65536").Delete
    sFName = "C:InfoForms" & Range("B2") & _
    "-Summary" & Format(Date, "mmddyyyy")
    MsgBox "Summary Sheet will be saved as " & sFName
    ActiveWorkbook.SaveAs (sFName)
    End Sub</pre>


  5. #5
    BakerMan
    Guest

    Re: Saving One Sheet into a separate workbook (2000)

    I got an error 400.

    The data I wanted removed was indeed removed, but the summary sheet went blank. The reason is I have vlookup formulas in the cells. It also created a new workbook, I'm not sure why, but Book2 popped up.

    If I save I get an error 400, but if I cancel the original workbook comes back. HMMMM
    Thanks for your help and patience.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving One Sheet into a separate workbook (2000)

    See if this does what you want:

    <pre>Sub SaveFile()
    Dim sFName As String
    Dim oSWB As Workbook, oDWB As Workbook, lNWBSheets As Long
    Set oSWB = ActiveWorkbook
    lNWBSheets = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Set oDWB = Workbooks.Add
    Application.SheetsInNewWorkbook = lNWBSheets
    oSWB.Worksheets(1).Range("A:E").Copy
    oDWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
    oSWB.Worksheets("Sheet1").Range("A1:E30").Copy
    oDWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    sFName = "C:InfoForms" & oDWB.Range("B2") & Format(Date, "mmddyyyy")
    MsgBox "File will be saved as " & sFName
    oDWB.SaveAs (sFName)
    End Sub
    </pre>

    Legare Coleman

  7. #7
    BakerMan
    Guest

    Re: Saving One Sheet into a separate workbook (2000)

    Legare, it deletes the extra data, but does not save to the new file. I get:

    Object doesn't support this property or method (Error 438)--Below is the HELP topic it refered me too, which, needless to say, didn't help me much. I appreciate your help, and it's almost there. This cuts my kb from over 200 to 15.

    Not all objects support all properties and methods. This error has the following cause and solution:
    You specified a method or property that doesn't exist for this Automation object.
    See the object's documentation for more information on the object and check the spellings of properties and methods.
    You specified a Friend procedure to be called late bound.
    The name of a Friend procedure must be known at compile time. It can't appear in a late-bound call.
    For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving One Sheet into a separate workbook (2000)

    Sorry, I didn't actually test the last two lines and I omitted a necessary property. Try this:

    <pre>Sub SaveFile()
    Dim sFName As String
    Dim oSWB As Workbook, oDWB As Workbook, lNWBSheets As Long
    Set oSWB = ActiveWorkbook
    lNWBSheets = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Set oDWB = Workbooks.Add
    Application.SheetsInNewWorkbook = lNWBSheets
    oSWB.Worksheets(1).Range("A:E").Copy
    oDWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
    oSWB.Worksheets("Sheet1").Range("A1:E30").Copy
    oDWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    sFName = "C:InfoForms" & oDWB.Worksheets(1).Range("B2") & Format(Date, "mmddyyyy")
    MsgBox "File will be saved as " & sFName
    oDWB.SaveAs (sFName)
    End Sub
    </pre>

    Legare Coleman

  9. #9
    BakerMan
    Guest

    Re: Saving One Sheet into a separate workbook (2000)

    That works very well, thankyou <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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