Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro automation error and paste value (excel)

    I have a macro here which works by going through a folder and extracting individual and a selection of cells. I am getting an automation error when the macro steps into picking a selection of cells to copy. The first senerio works, but any copying senerios after that does not. The first attempt to copy and paste a selection that is working is printing #REF! though; is there someting im missing in my code to call the value instead of the formula? and is there a noticble error in my code that is causing an automation error?

    Thanks,

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro automation error and paste value (excel)

    i have attached an example which the macro can be ran on, just change the worksheet to CBM Summary1.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro automation error and paste value (excel)

    Your code has the following statement numerous times:

    wbk.Close SaveChanges:=False

    Those statements are followed by statements that use wbk. You can't use a workbook after you close it.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro automation error and paste value (excel)

    I think you need a paste special to paste values instead of #REF! I looked this up in help. maybe it will help:

    newSheet.Range("A1").PasteSpecial Paste:=xlValues

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro automation error and paste value (excel)

    Yep I see the problem,

    If the values I am copying are based on a formula, how is it possible to copy and paste them as just values instead of references to the formula?

    thanks for the help

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro automation error and paste value (excel)

    You must use the PasteSpecial method instead of Paste. Your first Paste would look something like this to paste the value:

    wsh.Range("M" & i).PasteSpecial Paste:=xlPasteValues
    Legare Coleman

Posting Permissions

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