Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Clearing everything from a worksheet copy (2000/2002)

    I have some VBA code that copies a worksheet with the following:
    Sheets(myTemplate).Copy Before:=Sheets(1)
    This is done so that changes done during the process are done to a copy and not the original worksheet.

    The original worksheet has not only cells with values/functions but also Chart objects. The above copy method copies everything including the page layout.
    The next bit of code is as follows:
    Sheets(Targetsheet).Cells.Clear
    This clears the contents of all the cells on the copy however the Chart remains. The next line of code is:
    Sheets(myTemplate).Cells.Copy
    Sheets(Targetsheet).Cells.Select
    Sheets(Targetsheet).Paste
    This ends up copying the Chart objects a second time. This is not code I did myself and changing the logic behind it is not an option. What I would like is to find the best code to add after 'Sheets(Targetsheet).Cells.Clear' that would also remove the Chart objects.

    Help.

    Thanks
    Ed

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Clearing everything from a worksheet copy (2000/2002)

    Try

    ActiveSheet.ChartObjects.Delete

    or

    WorkSheets("SheetName").ChartObjects.Delete

    (I don't work with charts much.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Clearing everything from a worksheet copy (2000/2002)

    You could include teh following line after the Cell.Clear line

    Sheets(TargetSheet)ChartObjects.Delete

    Andrew C

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Clearing everything from a worksheet copy (2000/2002)

    Hi folks,

    Actually, you'd probably want to use something like:
    Sheets(Targetsheet).Shapes.Delete
    since the copying activity would duplicate any other shapes (eg text boxes, drawing shapes etc) that had previously been copied across from the source sheet too.

    An alternative would be to change the Sheets(Targetsheet).Paste action to:
    Sheets(Targetsheet).PasteSpecial Paste:=xlFormulas ' and
    Sheets(Targetsheet).PasteSpecial Paste:=xlPasteFormats
    This would avoid the need to delete any shapes and has the advantage that, if you've added some to the target sheet that aren't on the source sheet, they will be preserved. The disadvantage is that any new shapes from the source sheets won't be copied.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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