+ Reply to Thread
Results 1 to 2 of 2

Save copy of active sheet - values and formats only

  1. #1
    Lance Fairway
    Guest

    Save copy of active sheet - values and formats only

    I use the code below to save a copy of my Workbook in a specific file
    path (testing to see if the folders already exist along the way) with a
    specific, data dependent file name. It works, but I'd like to tweak it
    a bit and I'm not sure how to write the code.

    I'd like to save only the active sheet instead of the whole workbook.
    I'd also like to save only the values and formats to the new file, not
    the formula and macros.

    Anyone have any advice on how to accomplish this?

    Here's my current code:

    Sub SaveName()
    If Not Len(Dir("g:\users\one\" & Range("h6"), vbDirectory)) <> 0 Then
    MkDir "h:\users\one\" & Range("h6")
    End If
    If Not Len(Dir("h:\users\one\" & Range("h6") & "\" & Range("e3"),
    vbDirectory)) <> 0 Then
    MkDir "h:\users\one\" & Range("h6") & "\" & Range("e3")
    End If
    If Not Len(Dir("h:\users\one\" & Range("h6") & "\" & Range("e3") & "\"
    & Range("g7"), vbDirectory)) <> 0 Then
    MkDir "h:\users\one\" & Range("h6") & "\" & Range("e3") & "\" &
    Range("g7") & "\"
    End If
    ActiveWorkbook.SaveAs Filename:="h:\users\one\" & Range("h6") & "\" &
    Range("e3") & "\" & Range("g7") & "\" & Range("e3") & "ControlSheet" &
    "." & Left(Range("b7"), 31) & ".xls"
    End Sub


  2. #2
    Stopher
    Guest

    Re: Save copy of active sheet - values and formats only

    You might have to do a cut and paste special into a new workbook with
    the sheets you want. Also with your code it seems that it is only
    looking at the active sheet in the active workbook as there are no
    sheet qualifiers in your code ie sheet1.Range("h6").
    Is this always the case or is it just grabbing a set of cells on some
    active sheet to create the concatenated file name, which you probably
    store on the sheet some where instead of the complex range + range you
    have?

    Just a few question I need to know really.


    Lance Fairway wrote:
    > I use the code below to save a copy of my Workbook in a specific file
    > path (testing to see if the folders already exist along the way) with a
    > specific, data dependent file name. It works, but I'd like to tweak it
    > a bit and I'm not sure how to write the code.
    >
    > I'd like to save only the active sheet instead of the whole workbook.
    > I'd also like to save only the values and formats to the new file, not
    > the formula and macros.
    >
    > Anyone have any advice on how to accomplish this?
    >
    > Here's my current code:
    >
    > Sub SaveName()
    > If Not Len(Dir("g:\users\one\" & Range("h6"), vbDirectory)) <> 0 Then
    > MkDir "h:\users\one\" & Range("h6")
    > End If
    > If Not Len(Dir("h:\users\one\" & Range("h6") & "\" & Range("e3"),
    > vbDirectory)) <> 0 Then
    > MkDir "h:\users\one\" & Range("h6") & "\" & Range("e3")
    > End If
    > If Not Len(Dir("h:\users\one\" & Range("h6") & "\" & Range("e3") & "\"
    > & Range("g7"), vbDirectory)) <> 0 Then
    > MkDir "h:\users\one\" & Range("h6") & "\" & Range("e3") & "\" &
    > Range("g7") & "\"
    > End If
    > ActiveWorkbook.SaveAs Filename:="h:\users\one\" & Range("h6") & "\" &
    > Range("e3") & "\" & Range("g7") & "\" & Range("e3") & "ControlSheet" &
    > "." & Left(Range("b7"), 31) & ".xls"
    > End Sub



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1