Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Save just a worksheet (excel 2003)

    Is there a way to just save a worksheet in a workbook as its own file by macro?

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

    Re: Save just a worksheet (excel 2003)

    The Copy method of a worksheet copies that worksheet to a new file; you can then save it:

    ' Copy a sheet to a new workbook
    Worksheets("Sheet3").Copy
    ' The new workbook has become the active workbook
    ActiveWorkbook.Close SaveChanges:=True, FileName:="NewFile.xls"

    Warning: if the worksheet has formulas that refer to other worksheets in the original workbook, you'll end up with external references. This may or may not be what you want.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save just a worksheet (excel 2003)

    Hi Hans thanks for your reply, I am having trouble though, here is what i have

    osaveto = "c:"
    CFN = CFN = osaveto & "" & oProgramNumber & " " & oCompanyName & " " & oLocation & ".cfg"

    Worksheets("WellDataExport").Copy
    ActiveWorkbook.Close SaveChanges:=True, Filename:=CFN

    The above is what Ihave dded to a macro, It seems to have trouble right at the save process. I get the message "Application-defined or object-defined error"

    what could be causing this?

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

    Re: Save just a worksheet (excel 2003)

    The line

    CFN = CFN = ...

    contains CFN = twice. Remove one of the occurrences.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save just a worksheet (excel 2003)

    sorry that was a typo, in my post.

    any other things that could cause the problem?

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

    Re: Save just a worksheet (excel 2003)

    Remove one of the backslashes. Setting

    osaveto = "c:"
    CFN = osaveto & "" & ...

    results in "c:..."

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

    Re: Save just a worksheet (excel 2003)

    BTW by using this method, you're saving the worksheet in Excel format even though you specify .cfg as extension. If you want to save as a text file, use code like this:

    ...
    CFN = ...
    Worksheets("WellDataExport").Copy
    ActiveWorkbook.SaveAs Filename:=CFN, FileFormat:=xlTextWindows
    ActiveWorkbook.Close SaveChanges:=False

  8. #8
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save just a worksheet (excel 2003)

    ok Thanks for you wisdom

Posting Permissions

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