Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2006
    Location
    Wichita, Kansas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste special affecting graph formatting (2003/SP1)

    Edited by HansV to reduce large screenshot in size - please don't post pictures more than 640 pixels wide.

    I am attempting to write a macro to do a paste special (values only) for me. I have a series of data that I am trying to shift one cell to the left every month, to insert new data into the last cell to overwrite the data in the last cell. The graph updates just fine after doing the paste special, but the actual data bars (its a vertical bar graph), go from about a half inch from what they were, to a skinny line, the date format for the X-axis labels also go from a MMM-YY format to MM/DD/YY.

    I will attach a screenshot to illustrate my problem.

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

    Re: Paste special affecting graph formatting (2003/SP1)

    Welcome to Woody's Lounge!

    Could you attach a small sample workbook? Replace data with dummy data if necessary. Thanks.

  3. #3
    New Lounger
    Join Date
    Jul 2006
    Location
    Wichita, Kansas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste special affecting graph formatting (2003/SP1)

    Here you go. Mind you, I did not create this worksheet [img]/forums/images/smilies/smile.gif[/img] I was only trying to make it a little easier for them to update it.

  4. #4
    New Lounger
    Join Date
    Jul 2006
    Location
    Wichita, Kansas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste special affecting graph formatting (2003/SP1)

    Though, I did notice that the data does not update inside the graph, even though the links themselves have not changed.

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

    Re: Paste special affecting graph formatting (2003/SP1)

    The problem is that after the shift, there are two equal dates in the x values. This messes up things. Setting the date will correct this.

    Sub ShiftData()
    Range("C51:N56").Copy
    Range("B51").PasteSpecial xlPasteValues
    Range("N51") = DateSerial(Year(Range("N51")), Month(Range("N51")) + 2, 0)
    End Sub

    Note: there are some problems with your workbook. I get an error message when I open it, and there are mysterious external links.

  6. #6
    New Lounger
    Join Date
    Jul 2006
    Location
    Wichita, Kansas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste special affecting graph formatting (2003/SP1)

    The original workbook was created for us by a third party company, I'm sure they had their data linked to a different file and never broke the link before they sent it to us. I may have to recreate the entire workbook to avoid this error.

    Works beautifully BTW. I'm not very good with creating macros, but I did my fair share of VB coding, this makes a lot more sense to me. Thank you for providing the script!

Posting Permissions

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