Results 1 to 9 of 9

Thread: Copy codes

  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Copy codes

    I have codes RS, NR,& VD on sheet FPSTAT1 & FPSTAT2 on Col AP

    I have tried to adapt the code obtained from this forum but cannot get the data to copy correctly :


    1) Where the RS appears on sheets FPSTAT1 & FPSTAT2 (Col AP), then to copy the data from A19 to the last row in Col N where col AP contains RS to A10(have written code to first clear data, except data colored in blue)
    2) Where the NR appears on sheets FPSTAT1 & FPSTAT2 (Col AP), then to copy the data from A19 to the last row in Col N where col AP contains NR to A21
    (have written code to first clear data, except data colored in blue)
    3) Where the VD appears on sheets FPSTAT1 & FPSTAT2 (Col AP), then to copy the data from A19 to the last row in Col N where col AP contains VD to A35
    (have written code to first clear data, except data colored in blue)


    It would be appreciated if someone could kindly assist me
    Attached Files Attached Files
    Last edited by HowardC; 2016-10-17 at 12:47.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    It seems we need a bit more help with what you want to do.
    Re:
    1) Where the RS appears on sheets FPSTAT1 & FPSTAT2 (Col AP), then to copy the data from A19 to the last row in Col N where col AP contains RS to A10(have written code to first clear data, except data colored in blue)
    Copy the data from what sheet??? both sheets??
    Can you post another file showing what you would expect to see after the copying is done.

    zeddy
    Never Give In.
    Never Give Up.
    Never Go Back.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply

    I need the data copied from sheets FPSTAT1 & FPSTAT2 to sheet recon items as follows:

    Before the data is copied to sheet "Recon Items" , the data on this sheet must be cleared, except the items coloured in blue



    1) Where the RS appears on sheets FPSTAT1 & FPSTAT2 (Col AP), then to copy the data from A19 to the last row in Col N where col AP contains RS to A10 on sheet "Recon Items"
    2) Where the NR appears on sheets FPSTAT1 & FPSTAT2 (Col AP), then to copy the data from A19 to the last row in Col N where col AP contains NR to A21
    3) Where the VD appears on sheets FPSTAT1 & FPSTAT2 (Col AP), then to copy the data from A19 to the last row in Col N where col AP contains VD to A35

    I have attached a new workbook and have manually copied the items
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    Thanks for the additional info.
    I'll have a look and post back tomorrow.

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks. Look forward to receiving your codes so I can study these

    Howard

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    I went back to the first file you posted after seeing your second example.

    See attached file.

    Instead of 'looping' through the rows to check for RS, NR and VD, I used a 'filter' method to copy the matching data.
    My vba can be simplified further, but I thought I'd leave it as is for you to look at first.

    zeddy
    Attached Files Attached Files

  7. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2016-10-19)

  8. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the help

    Your code works perfectly

    I just need one change. Where VD appears in Sheets FPSTAT1 & FPSTAT2 in Col AP & the Value in Col N > 0, then copy the data from A19 to the last row in Col N to A35 onwards on Sheet "Recon Items"


    Your assistance is most appreciated


    Howard
    Last edited by HowardC; 2016-10-19 at 13:16.

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    In my posted file in post#6, I should've made sure that there were no hidden columns on sheets [FPStat1] and [FPStat2].
    see updated file attached where columns have been unhidden.
    This ensures proper alignment of the data copied to sheet [Recon Items]

    For your additional request, it would be much easier to filter these sheets for multiple conditions, i.e. both col N > 0 AND col AP = "VD" , if we had 'non-empty headings' in row 18 i.e. from B18 to AP18

    I appreciate that you may have deleted some real data in your 'sample' posted file, but do you have non-blank headings in row 18????

    zeddy
    Attached Files Attached Files

  10. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for your help and input

    I will insert headings in B18 to AP18


    Howard

Posting Permissions

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