Saving Worksheet as New WorkBook VBA

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Saving Worksheet as New WorkBook VBA

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Saving Worksheet as New WorkBook VBA

     
    I have the following code to save a sheet in my current workbook to a new workbook.

    Sub Sheet_SaveAs()

    Dim wb As Workbook

    Sheets("Audit Sheet").Copy

    Set wb = ActiveWorkbook

    With wb
    .SaveAs Format(Date, "yymmdd") & "_REP_" & Range("M4") & "_" & Range("M3") & "_" & Range("AP3")
    .Close False
    End With

    End Sub


    This saves the new workbook in the default location of my documents but I want to specify the folder that it goes to.

    I am new to VBA so any help will be much appreciated.

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,458
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

    Hi, welcome to the forum!

    Quote Originally Posted by Wes4444 View Post
    I want to specify the folder that it goes to.
    Specify how?
    -By letting the user choose the folder on the fly?
    -By storing the folder in a cell on the worksheet?
    -By hardcoding the folder directly in the code?
    -Something else?
    Last edited by FormR; Dec 7th, 2017 at 06:03 AM.
    [code]your code[/code]

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

    Thankyou for such a quick reply!

    I don't want the user to be able to choose the folder, I just want the Workbook to be sent to the same folder every time. So if I was able to put the folders location into the code that would be perfect.

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,458
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

    Hi, then you just simply need to include the path in front of the file name, for example:

    Code:
    Sub Sheet_SaveAs()
     Dim wb As Workbook
     Sheets("Audit Sheet").Copy
     Set wb = ActiveWorkbook
     With wb
      .SaveAs "C:\MyFolder\" & Format(Date, "yymmdd") & "_REP_" & Range("M4") & "_" & Range("M3") & "_" & Range("AP3")
      .Close False
     End With
     End Sub
    Last edited by FormR; Dec 7th, 2017 at 06:14 AM.
    [code]your code[/code]

  5. #5
    New Member
    Join Date
    Dec 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

    Perfect that worked but it also changed the name of the new worksheet by adding the folder name at the begining. Is there a way to do it by keeping the original name without the folder name?

  6. #6
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,458
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

    Quote Originally Posted by Wes4444 View Post
    but it also changed the name of the new worksheet by adding the folder name at the begining.
    Hi, I don't understand - that code doesn't change the name of the worksheet at all.
    [code]your code[/code]

  7. #7
    New Member
    Join Date
    Dec 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

    My apologies I simply forgot to put in the backslash at the end of the path.

    Works perfectly thankyou for your help!

  8. #8
    New Member
    Join Date
    Dec 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

    Sorry one more question. How do I do the same process but save the worksheet as a pdf rather than an excel file?

  9. #9
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,458
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

    Something along of the lines of:

    Code:
    Sub Sheet_SaveAs()
    Sheets("Audit Sheet").ExportAsFixedFormat xlTypePDF, "C:\Folder Name\" & Format(Date, "yymmdd") & "_REP_" & Range("M4") & "_" & Range("M3") & "_" & Range("AP3")
    End Sub
    [code]your code[/code]

  10. #10
    New Member
    Join Date
    Dec 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Saving Worksheet as New WorkBook VBA

      
    Works a charm thankyou!

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com