Results 1 to 4 of 4
Like Tree1Likes
  • 1 Post By kidwispa

VBA to copy worksheet to new workbook and save

This is a discussion on VBA to copy worksheet to new workbook and save within the Excel Questions forums, part of the Question Forums category; Hi All, I asked a similar question on this thread but have managed to get most of the problem solved. ...

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Location
    Solihull, UK
    Posts
    310

    Default VBA to copy worksheet to new workbook and save

    Hi All,

    I asked a similar question on this thread but have managed to get most of the problem solved. The only bit I am struggling with now is how to copy the entire worksheet into a new workbook and save the file to a specific directory with the following filename format ("exceptions191011 - ie. the word exceptions followed by today's date in ddmmyy format).

    Here is what I've got so far:

    Code:
     
    Sub SaveAs()
     
        Dim FName           As String
        Dim FPath           As String
     
        FPath = "G:\Exceptions\"
        FName = "Exceptions" & Format(Date, "ddmmyy") & ".xls"
     
        Sheets("DataSort").Copy
        ThisWorkbook.Sheets("Sheet1").SaveAs Filename:=FPath & "\" & FName
     
    End Sub
    Thanks in advance for any suggestions

    EDIT - This now seems to work - could someone please help me add the following condition that if a file already exists with that name then to bring up an error message stating the file already exists?

    2ND EDIT - As well as save the new workbook, it appears to close the original workbook - can I stop this?



    Last edited by kidwispa; Oct 19th, 2011 at 05:02 AM. Reason: It doesnt work!
    Excel 2010 (64-bit)

  2. #2
    Board Regular
    Join Date
    Aug 2008
    Posts
    314

    Default Re: VBA to copy worksheet to new workbook and save

    If you use ThisWorkbook then you are saving the workbook with the code in it rather than the new one you've created. This will be with the new name (Exceptions etc). That's why the it appears that the original workbook is being closed. The following code should do all you want.

    Code:
    Sub SaveAs()
     
        Dim FName           As String
        Dim FPath           As String
        Dim NewBook         As Workbook
     
        FPath = "G:\Exceptions"
        FName = "Exceptions" & Format(Date, "ddmmyy") & ".xls"
     
        Set NewBook = Workbooks.Add
     
        ThisWorkbook.Sheets("DataSort").Copy Before:=NewBook.Sheets(1)
     
        If Dir(FPath & "\" & FName) <> "" Then
            MsgBox "File " & FPath & "\" & FName & " already exists"
        Else
            NewBook.SaveAs Filename:=FPath & "\" & FName
        End If
     
    End Sub

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Location
    Solihull, UK
    Posts
    310

    Default Re: VBA to copy worksheet to new workbook and save

    Thanks - will give it a try
    DaveU likes this.
    Excel 2010 (64-bit)

  4. #4
    New Member
    Join Date
    Aug 2011
    Location
    Waldeck, Sk, Canada
    Posts
    10

    Default Re: VBA to copy worksheet to new workbook and save

    Hi gsbelbin,

    Thanks for this. I was running this bit of code originally:

    Sheets(1).Copy
    Set Wb = ActiveWorkbook
    Wb.SaveAs . . .

    Running this in xl2003 on some very old (slow) machines, occasionally Wb would get set to ThisWorkbook, and that's what would get saved, instead of the newly created workbook. Appeared to be a bit of a "race" going on, with Wb getting set before the new workbook appeared on screen. With your code, I'm now able to ensure that Wb is set correctly. Thank you.

    Regards,

    Dave
    Last edited by DaveU; Oct 12th, 2013 at 12:20 AM. Reason: typo

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