Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save Worksheet as new work book (2000/2003)

    I searched for this but alas - no luck, I know I've seen it here before. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    I have a workbook with multiple sheets. I would like a macro to save the active sheet to a new workbook.
    I would like to be able to name the new workbook from cell contents from the original workbook. Any help is appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Worksheet as new work book (2000/2003)

    Say that you want to save the new workbook in the default Excel folder, or in the currently active folder, and that you want to take the name from cell A2 in the active sheet. The following macro will do that:

    Sub SaveActiveSheet()
    Dim strName As String
    strName = ActiveSheet.Range("A2")
    ActiveSheet.Copy
    ActiveWorkbook.Close SaveChanges:=True, Filename:=strName
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as new work book (2000/2003)

    And how would I get this to save to my desk top?...thanks

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save Worksheet as new work book (2000/2003)

    Try this

    Sub SaveActiveSheet()
    Dim strName As String
    strName = ActiveSheet.Range("A2")
    ActiveSheet.Copy
    ActiveWorkbook.Close SaveChanges:=True, Filename:="Cocuments and SettingsstansDesktop" & strName & ".xls"
    End Sub

    Oviously change stans to your account name
    Jerry

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as new work book (2000/2003)

    Brilliant------Have a Choccy Bar on me
    thankyou <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save Worksheet as new work book (2000/2003)

    Cheers

    I thought I would add this little baby in case you are sharing it around with different users. It makes an API call and finds out the windows log in of the current user.



    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Function UserNameWindows() As String

    Dim lngLen As Long
    Dim strBuffer As String

    Const dhcMaxUserName = 255

    strBuffer = Space(dhcMaxUserName)
    lngLen = dhcMaxUserName
    If CBool(GetUserName(strBuffer, lngLen)) Then
    UserNameWindows = Left$(strBuffer, lngLen - 1)
    Else
    UserNameWindows = ""
    End If
    End Function

    Sub SaveActiveSheet()
    Dim strName As String
    Dim UserName As String

    UserName = UserNameWindows

    strName = ActiveSheet.Range("A2")
    ActiveSheet.Copy
    ActiveWorkbook.Close SaveChanges:=True, Filename:="Cocuments and Settings" & UserName & "Desktop" & strName & ".xls"
    End Sub
    Jerry

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as new work book (2000/2003)

    You are correct, It will be used on more than one computer. (Not on a server, individual machines)

    Where do I place the Function though? I'm not familiar with using functions. Does the function run automatically?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save Worksheet as new work book (2000/2003)

    <P ID="edit" class=small>(Edited by Jezza on 15-Jul-07 15:29. Added workbook)</P>Same place as the sub

    1) Alt + F11
    2) In lefthand pane, Right mouse click ThisWorkbook
    3) Insert | Module
    4) Paste all of the code I gave you into the new module

    FYI

    SaveActiveSheet calls UserNameWindows to get the required data.
    Jerry

  9. #9
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as new work book (2000/2003)

    I must have it in wrong I got the following error

  10. #10
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as new work book (2000/2003)

    <P ID="nt"><font size=-1>(No Text)</font>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save Worksheet as new work book (2000/2003)

    You didn't follow my instructions fully, see # 4 above and coly all the code, otherwise I have attached a workbook to the above post now for you to see
    Jerry

  12. #12
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as new work book (2000/2003)

    That's perfect, it was my fault I thought the code began with the Function line so I missed copyng the top one - thank you so much this makes it all much easier.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as new work book (2000/2003)

    Everythings still working great but I have another question:

    The active sheet cells that the save name is referenced too (A2) have a formula that gets the values from another sheet.

    When I save it using the macro, how would I make it just copy the value of A2 rather that the formula?
    After I save the workbook, I want to be able to email it to several supervisors who won't have the original workbook.

    I could reference the ave name to another sheet in the workbook I tried changing theline to:
    strName = ActiveSheet.Range("otherbook!A2" but it dosn't work thanks

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save Worksheet as new work book (2000/2003)

    Use:

    strName = Worksheets("Sheet2").Range("A2")

    Change Sheet2 to the name of the sheet you are referencing
    Jerry

  15. #15
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Worksheet as new work book (2000/2003)

    That answered my question, But the new book thatI save from the activesheet still asks to update links back to the original workbook, is there a way around that?

    thanks for all the help on this Jerry.

Page 1 of 2 12 LastLast

Posting Permissions

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