Results 1 to 8 of 8
  1. #1
    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

    I want to save it here..... (XL 97-->XP)

    Hi All

    I have a workbook and I want to save each worksheet as a seperate workbook in a specific network folder. I have written this piece of code, but it defaults to My Documents as expected. How can I get it to save to a network folder without changing the user default option?

    Sub Saver()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsName As String

    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
    ws.Copy

    Set wb = ActiveWorkbook

    With wb

    .SaveAs FileName:=ActiveSheet.Name


    End With
    Next ws

    End Sub



    Jerry
    Jerry

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I want to save it here..... (XL 97-->XP)

    Change the line:

    .SaveAs FileName:=ActiveSheet.Name

    to something like:

    .SaveAs FileName:= "H:FolderAFolderB" & ActiveSheet.Name

  3. #3
    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: I want to save it here..... (XL 97-->XP)

    Ok Guys

    Sorry to waste your time I have solved it.

    Sub Saver()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsName As String

    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
    ws.Copy

    Set wb = ActiveWorkbook

    With wb

    .SaveAs FileName:= ("C:WindowsDesktopMyFolder" & ActiveSheet.Name)


    End With
    Next ws

    End Sub

    The path can be changed to wherever you want it

    Hope this may be useful to others


    Jerry
    Jerry

  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: I want to save it here..... (XL 97-->XP)

    Thanks Brooke

    You beat me by two minutes with the answer!!!!

    Jerry
    Jerry

  5. #5
    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: I want to save it here..... (XL 97-->XP)

    Hi Brooke

    Just a small thing.

    Now I have run the code succesfully. The new workbooks remain open, how do I close it each one as and when the code saves it?

    Jerry
    Jerry

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I want to save it here..... (XL 97-->XP)

    add the line

    Activeworkbook.Close

    immediately after the .SaveAs line.

  7. #7
    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: I want to save it here..... (XL 97-->XP)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thanks, I was thinking Activeworkbook.Close would close the whole workbook not just the current worksheet.

    Jerry
    Jerry

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

    Re: I want to save it here..... (XL 97-->XP)

    ActiveWorkbook.Close does close the whole workbook, not just the worksheet. At that point you have two workbooks open. The original workbook and a new one that contains the single worksheet that you copied.
    Legare Coleman

Posting Permissions

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