Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    PasteValue Problem (Excel 2002)

    Hi

    The attached code kindly supplied by Hans, works fine except for one column.

    Below are two examples of the results of which are being pasted

    "Column C" =IF(ISNA(VLOOKUP(B7,INDIRECT(VLOOKUP($C$5,dccodes, 2,0)),2,0)),"",VLOOKUP(B7,INDIRECT(VLOOKUP($C$5,dc codes,2,0)),2,0)) ,result 004.508, pastes as a #REF


    "Column D" =IF(B7<>"",VLOOKUP(B7,Chem_Table,Min_UK,FALSE)," ") pastes as values, result
    If you are a fool at forty, you will always be a fool

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

    Re: PasteValue Problem (Excel 2002)

    How do you expect us to solve this without seeing the data?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: PasteValue Problem (Excel 2002)

    Hi Hans

    What do you actually need to see?


    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: PasteValue Problem (Excel 2002)

    The original of the worksheet that causes problems. You can alter sensitive data.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: PasteValue Problem (Excel 2002)

    Hi Hans

    I hope this will help.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: PasteValue Problem (Excel 2002)

    Cell C7 in both worksheets in the workbook you attached contains a formula that returns #REF. The macro will not repair that.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: PasteValue Problem (Excel 2002)

    Hi Hans

    This is how it looks before I copy and paste.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: PasteValue Problem (Excel 2002)

    Then you'll need to attach the workbook as it was before copy and paste.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: PasteValue Problem (Excel 2002)

    Hi Hans

    The workbook is much to large to post here.

    So as not to try your patience any longer, I better give up on this one.

    Thank you for your efforts.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: PasteValue Problem (Excel 2002)

    I'm sorry, I'd like to help, but there's not much I can do without adequate information.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: PasteValue Problem (Excel 2002)

    Hi Hans

    I have made another attempt to post this problem.

    As you will see C7 in chemicals shows the result of a formula but pastes as a #REF, whereas all the other formulas which I have removed all paste as values.

    NB: if you need a password it is wahsdarb.

    Heres Hoping

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: PasteValue Problem (Excel 2002)

    The problem is that the named ranges are not valid any more in the new workbook. The attached code takes a different approach: it copies ALL sheets, replaces formulas with values and then removes the unwanted sheets.

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: PasteValue Problem (Excel 2002)

    Hi Hans

    I used your ammended code, and it stopped in error here: wsT.Range("A1").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True, (this was highlighted in yellow.)

    Many thanks for your prompt reply.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: PasteValue Problem (Excel 2002)

    The code works correctly in the workbook you attached. What was the text of the error message?

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: PasteValue Problem (Excel 2002)

    Hi Hans

    The error message I was getting was Runtime Error Select Method of Range Class Failed. So I removed all these and then it ran OK.

    Except I still get the #REF see screenshot.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 LastLast

Posting Permissions

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