Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Macro to insert rows & Copy Data

    I need a macro to do the folowing:


    I need rows to be inserted on sheet1 after the number in Col B. The number of rows to be inserted must be compared to the account number in Col F on sheet2 and if there is a match , then the the number of items in Col G to O on Sheet2 must be counted and the applicable number of rows to be inserted For Eg if KTR-3000 is in F2 and there are no items in G2 to O2 then no rows to be inserted after KRT-3000 on sheet1. If KTR-4000A has only one item in G2 to O2 , then one row to be inserted after KTR-4000A


    I have attached my sample file


    It would be appreciated if someone could kindly assist
    Attached Files Attached Files

  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

    ..there are simpler formulas that can be used, which I will show in my posted file.
    I'm not quite ready to post yet, as I'm still trying to work out what you are trying to exactly do on your first sheet.
    Hopefully, I'll post a file for you tomorrow.

    zeddy
    PS Have you ever eaten a Cadillac? I understand it's a large pear. Truthfully. None in any shops around here though.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Thanks Zeddy

    Look forward to receiving your file

  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
    I need rows to be inserted on sheet1 after the number in Col B. The number of rows to be inserted must be compared to the account number in Col F on sheet2 ..
    zeddy:I think you mean col E???

    ..and if there is a match , then the the number of items in Col G to O on Sheet2 must be counted and the applicable number of rows to be inserted For Eg if KTR-3000 is in F2
    zeddy:I think you mean E2???

    On your sheet [Sales Groupings] in your posted file, there are 'gaps' i.e. some 'empty rows' in the row range from rows 2:210
    Also, on your sheet [Sales Groupings] , none of the account numbers (both New and Old), from row 99 downwards, can be found anywhere on sheet [Account Extraction].

    The Old account numbers in col [B] rows 53 to 68 on your sheet [Sales Groupings], can be found in col [E], rows 42 to 57, on sheet [Account Extraction], but they don't have their corresponding New Acccount Numbers (although these are shown on sheet [Sales Groupings] )???

    Could you have another look at your posted file, and perhaps re-post another example file?
    I don't want to post my vba until I understand more clearly what you need.

    zeddy

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


    I have attached a new workbook



    1) I need to compare the account numbers in Col E on sheet “Account extraction” to Col B “Raw Data” . Where there is a match i.e account numbers are the same(Col E “account extraction” matched Col B Raw Data) , then count the number of non-blank items in Col G to O and insert the number of rows below the applicable account number on sheet "Raw Data" For eg E27 (a/c KTR-30F) , the count is zero from G2:O2 , therefore no rows to be inserted. E2 (a/c KTR-00)
    the count is 8, therefore 8 rows to be inserted below KTR-00 etc
    2) Once all the rows how been inserted, then the account numbers In Col F :O on sheet “Account extraction” must be copied to sheet “Raw Data” to Col A where the account numbers In Col E (“Account extraction”) matches the account number in Col B "Raw Data"

    Please advise if you need any further info to make it clearer
    Attached Files Attached Files

  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

    here's my version of a solution.

    To process the accounts, click the small grey button in cell [B1] on sheet [Raw Data]

    I ignored the multiple occurences of the same 'old account number' in column [B] on sheet [Raw Data]
    (e.g. cells [B20], [B21] etc etc)

    I also used a completely different method of doing this, so only needed volumns [A] and [B] on the second sheet.

    zeddy
    Attached Files Attached Files

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

    HowardC (2016-08-26)

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

    Thanks very much. This is brialliant and exactly what I am looking for

Posting Permissions

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