+ Reply to Thread
Results 1 to 11 of 11

How to code a IF, Copy and Paste Macro in VBA? Is it possible?

  1. #1
    Registered User
    Join Date
    03-31-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    Hi All,

    I recently started to learn VBA coding. I am far from having a advanced skill set or knowledge on it. But I would like to know if my end goal is possible. I have a HUGE spreadsheet with data in Columns, Table Format. This file gets updated once a month and the update process is manual and very exhausting. Now the process i go trough every time is the Following:
    1. Run a report for created dates for "claims" from current date and 4 months back.
    2. I copy and paste all the relevant columns to my data from the new report (temporary file) to the main file (Huge fail).
    I don't have any problem with the first two steps I basically copy and past about 4-6 columns of new and repeated data.
    3. I sort by a "claim number" from smallest (older dates) to largest (most recent dates), this number is unique for each "claim".
    On this step I use Conditional Formatting to Highlight all the repeated claim numbers.
    4. Verify all claim numbers and update the "Status" (Closed,Open, etc...), update any of the columns that have changed for each repeated claim number.

    The last step is the one I am interested in automating.
    What I have been using to make this step less overwhelming is to add 2 extra columns and the first one is a "IF, OR" Statment where it looks at a cell and looks the the value on top and under it, if they are the same it ives me TRUE if not then FALSE. This column helps me on filtering for all the repeated claim numbers. The repeated claim number is always under the "real or true claim number". I say repeated because the row in which the repeat. I also compare the top and bottoms so this way when I filter it leaves the top and bottom numbers. Then the second column I add uses a "IF, AND" statement where it looks at all the columns I manually update and if the selected cells are the same as the one under it(all of them) it gives me "DELETE" if not then "REVISE". Once I add these columns I Copy and Special Paste Values on them and then sort for the Delete or Revise Column and delete the "DELETE" Rows. Once ALL of this is done then I look at every single repeated claim number and look at the columns to the right and left of it if they have changed I copy them and paste from the "Temporary" Row to the "PERMANENT or MAIN" Row, then I proceed to delete the temporary row. I know how to do this on the columns and what I would like VBA to do is.

    Given a starting cell and a ending cell which in terms is a range from A"starting cell":A"Ending Cell". Then Look at every claim number and compare it to the one below it if they are the same then look the columns that need update if they are different then have it copy the below value and paste it on the top value and then delete the temporary row which is the row under the cell he is looking at. Then it would move to the next claim number and repeat if the claim number is the same as the one below.

    From what I know I don't know how to make VBA ask for the starting and ending points. I think with a simple IF, THEN statement it could be solved, but with my super limited knowledge I still have not been able to grasp the coding. But the question is if VBA can do this. Look at a cell compare it to the one below and if they are the same look at the cells that i need to update usually in the left and right of the main cell, compare them to the cells under it copy and paste if they are different and then delete them and repeated with the rest. Also if the claim numbers are the same and nothing has changed then have it also delete the temporary row.

    On the flip side I think it could be easier if I just tell VBA to look at claim number compare it to the one below and if they are the same then just copy and past all the cells I update regardless if they are different or not and then delete the temporary row. This way I wont have to nest codes over codes. I would be the more simple version of the program. Like I said I am learning and I don't know how to do it yet. But is it possible?

    Thank you all in advance for reading this extensive explanation, and for you very valuable time and feedback. I started learning some of the basics on Chandoo and it gave me ideas on how to work with it, I have had some previous college experience of coding with matlab but I just know concepts not how to code. If you could also recommend books or other links that have tutorials I appreciate it too.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    The following example code might help

    Please Login or Register  to view this content.
    I'm assuming that the claim number is in column A. The code works upwards from the bottom of the data set and checks if both the claim number above and below is the same as the current cell in column A. If so it deletes the row. The result is that you only have first and last rows remaining.

    Starting at the bottom of the data set is a trick which makes the code a bit simpler as the rows yet to be worked upon do not move due to any deletions.

    You might now be able to adapt this code to deal with the other rules that you want to apply.

    It's always easier if you can post an example of the real data then assumptions don't have to be made.
    Martin

  3. #3
    Registered User
    Join Date
    03-31-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    So this is a small example of what the data would look like once I sort them by the claim number. For repeated claim number you see the some of the columns change. When I sort them the temporary row always ends on the bottom of the main row. The row that has empty on the product column is the temporary row. I usually update the status, Key Code and Closed dates for every repeated claim number. Once I Copy and Paste the bottom data to the top data I delete the entire temporary row. Creation Date never changes. But I was thinking it would be better If the code program just copies and paste the bottom rows to the selected row and then delete. For every repeated claim number in Column E in this case Copy and paste Columns for Status, Key Code and Closed date. I would need a way to tell the code to stop or up to what point I want it to run. Because this data has over 50,000 rows and if it runs from bottom to top I am not certain the computer would handle it. I am still learning all of this is way over my head. Where should I start to better understand peoples suggestions.

    Example Data.xlsx
    Last edited by EsaiMB; 03-31-2014 at 02:00 PM.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    The general idea would be this - where the claim number is in column C (you don't say what it is, actually)

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    Try this one....

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-31-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    Quote Originally Posted by Bernie Deitrick View Post
    Try this one....

    Please Login or Register  to view this content.


    This one works Great, It is not Updating Column D (Key Code).

    In the actual data set the columns are not in this exact order, how would I mold it to my data.

    If is to much to ask I can try and figure it out, but I don't know a single thing on what any of those mean.

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    Quote Originally Posted by EsaiMB View Post
    how would I mold it to my data.
    Post an actual data file, with sensitive data removed, showing one sheet with "before" and one sheet with "after"

  8. #8
    Registered User
    Join Date
    03-31-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    Example Data No Sensitive.xlsx
    Quote Originally Posted by Bernie Deitrick View Post
    Post an actual data file, with sensitive data removed, showing one sheet with "before" and one sheet with "after"
    The Programs should do the same as it did for the example but for more Columns. I really hope this does not become a burden to you. I cant do a before and after as the data is very extensive to do by hand. The Program would need to look at the Claim Number (Column AC). Compare it to either top and above and for the row it matches it (the temporary row) it has to copy and paste the columns highlighted in yellow and paste them or update the selected's data row. Columns K, O, S, AD, AQ, AZ, BA, BP, BQ, BR, BS, CA, CB, CC, CD. Now when I run the update there will be new claim numbers the new ones stay in the file and I since it wont be a repeat nothing would happen to it. For reference the temporary data as those that have a repeated claim number and in the product column is empty. The Main or permanent data will have a product name and the new ones wont have a product name and they wont have a repeated claim number. There are hidden columns feel free to un-hide them if needed.
    To me I think it would be best or easier to code if when ever it finds a repeated claim number to copy and paste those column cells, it cant copy and paste the whole row because some information is not obtain from the original report.

  9. #9
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    Try this version - it will take a while, since you are doing a lot of comparisons. Make sure that you have your data sorted the way that you want - that can also be part of this macro, or a separate macro.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-31-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    I will be using it with my data and I will let you know how it performs. Thank you for your time and effort.

    So after a couple of runs, changing the amount of data, it performs well but it only updates two fields and I guess from looking at the coding it is because of the line that says "Closed", It does Help me in the majority but it wont solve my problem. I would still need manual update because only 2 fields are being changed but only the word closed is the only one that gets updated. I can learn from this but I most certainly cant use it as it is. I mean no offense the code is very well executed and it works really fast if I only had to change the status to close and copy the closed date. I cant tailor it to my cause because I don't know half of what the coding means. Thank you very much I really appreciate your help. I don't want to keep you tied up to my cause.

    Would it be easier if I changed the Columns from where they are? Maybe if I put all the columns that need to be changed next to each this way the range would be from A:S or something like that. This way the code just looks at the Claim Number and if they are the same it just copies what is below and paste it on top and delete the temporary row. This way is not dependent of it being closed.

    Claim number is the same then I copy and I paste. Regardless if the other cells are different.
    Last edited by EsaiMB; 04-01-2014 at 11:29 AM. Reason: Updating

  11. #11
    Registered User
    Join Date
    03-31-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to code a IF, Copy and Paste Macro in VBA? Is it possible?

    Example Data No Sensitive.xlsx


    I fixed the way the columns look, now they are closer to each other. I guess the macro can be simplified by having it compare the Claim numbers and if they are repeated have it copy the temporary row and paste it to the one above, if there is no match then it ignores it and moves to the next cell. It sounds and looks so easy in my head but when I try to code it I have no idea on how to call out the variable and what events I need to use to create it. If someone could attached link that show how to use the necessary elements it would be easier for me to learn it.

    The Code above works but it only updates the status if it is closed and always updates the close data then erases the temporary row.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need a Macro code to copy and paste cells
    By mole_man in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-14-2014, 07:51 AM
  2. Macro Code to Copy paste Special
    By cartica in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-04-2013, 09:31 AM
  3. copy and paste using macro or code
    By excellover2212 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2013, 04:53 PM
  4. Macro Code Copy Paste Error
    By FLani in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-21-2013, 10:54 PM
  5. copy / paste based macro code
    By golfclubs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2010, 07:11 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1