+ Reply to Thread
Results 1 to 5 of 5

Help Requested with Application.Copy Options

  1. #1
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Help Requested with Application.Copy Options

    I have looked at a list of options available when copying an entire sheet (in my case, I will be saving it to a brand new file without Macros enabled).

    What I would like to do is use some conditional formatting in the original file to trigger the end user to check data where necessary... BUT it's absolutely vital that formatting doesn't get copied into the final file.

    Is there a way to NOT copy the conditional formatting? Or some other way to exclude it from the final file?


    Debbie

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Help Requested with Application.Copy Options

    When you copy the sheet to a new workbook, it will become the active workbook. So, you can change the formatting on the active sheet in the active workbook. That is, you can clear all the Conditional Formatting in the copied sheet.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Help Requested with Application.Copy Options

    Sub demo()
    ' copy sheet to new workbook
    Sheets("Sheet1").Copy

    ' Clear the conditional formats from copy of sheet
    Cells.FormatConditions.Delete
    End Sub

  4. #4
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Help Requested with Application.Copy Options

    So, what I have, then is this...

    Please Login or Register  to view this content.
    The only things I've done is to take out two constants (one in the FileName which I replaced with "Report" and one in the Copy line which I replaced with 1).

    Is this the correct way to use the FormatConditions.Delete coding?

  5. #5
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Help Requested with Application.Copy Options

    One change .... FileDate dim'd as string
    Another thing that I don't like doing is referring to a Sheet by it's number versus it's name.
    The Sheet index number is under the covers so when things go wrong the debugging for user,
    can be more difficult.

    For example if you delete "Sheet1" and then create again and give it the name "Sheet1" ....
    -- referring to it by in code by it's name "Sheet1" will still work
    -- referring to it by its index number "1" will not

    Sub ExportData()

    Dim FileName As String
    Dim FileDate As String
    Dim FilePath As String

    Application.DisplayAlerts = False
    Application.CopyObjectsWithCells = False 'to prevent the buttons from copying over
    ThisWorkbook.CheckCompatibility = False

    FileDate = Format((Date - 1), "mm-dd-yy")
    FilePath = ThisWorkbook.Path & "\"
    FileName = FilePath & "Report" & FileDate

    ThisWorkbook.Worksheets(1).Copy
    Cells.FormatConditions.Delete 'to clear any conditional formatting
    ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=xlOpenXMLWorkbook

    ActiveWorkbook.Close

    Application.CopyObjectsWithCells = True
    Application.DisplayAlerts = True

    End Sub 'ExportData
    Last edited by nimrod; 06-03-2016 at 11:18 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. (Unsolved) Look-up value in cell and copy-paste data - VBA Requested
    By spiwere in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-15-2015, 10:37 AM
  2. I need help to copy and paste the data as requested sheet2
    By venkatpvc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2013, 10:36 AM
  3. [SOLVED] Print Macro defaulting to Manual Tray 1 if more than 1 copy requested
    By Simon.Ward in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-03-2013, 04:12 PM
  4. Abort Options for Application.Filedialog
    By RoyMakaay1985 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2009, 07:39 AM
  5. Copy Function Options
    By jonn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2007, 08:23 AM
  6. [SOLVED] Copy and Paste Options
    By dstromain in forum Excel General
    Replies: 0
    Last Post: 10-13-2005, 12:05 AM
  7. Turn off Reviewing options at the application level
    By Tracey in forum Excel General
    Replies: 1
    Last Post: 08-05-2005, 08:05 PM

Tags for this Thread

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