Results 1 to 9 of 9

Thread: VBA code to copy and paste row based on certain criteria

  1. #1
    Junior Member
    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0

    VBA code to copy and paste row based on certain criteria

    Dear all,

    Currently I have an excel table where I have a macro that based on a certain value in a cell (> 1), it will copy, insert new row and paste the entire row x number of times based on the value in the column "COUNT".

    What I need help with is I don't want the row to be copied exactly, but based on the value in "COUNT", for each paste the row will contain only one input value with the following pattern. It's hard to explain, so I hope the table below explains it.

    BEFORE
    count input input input
    3 A A A
    AFTER
    3 A A A
    3 A A A
    3 A A A
    3 A A A

    what I would like the macro to do:

    BEFORE
    count input input input
    3 A A A
    AFTER
    3 A A A
    1 A
    1 A
    1 A

    So if the value is 10, I want the macro to copy it 10 times but each row has only one input value if an offset of 1.

    Attached is the excel with the current macro and an example how I wish it works.

    Thank you for your help!

    BR,

    T0NT0











    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 10-26-2023 at 12:18 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    For your last row in the BEFORE section, the data is starting after one column, whereas for row 5, even though the number is 2, the data is starting from the first adjacent column. Is there a pattern for this? Also, if the number is 10, does it mean that there will be 10 items in the adjacent column? And that it can start from any column in that row, and have a set of 10 adjacent cells filled?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    There is no pattern to the data. The range of columns with the data is quite larger than in my example, but you are correct.

    For example: The first data can start in any column, and then have the next 3 can have data, then again the next few columns will be empty, and then data again and so on.

    No pattern whats so ever. That what I can't figure out

    Thanks!

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-26-2023 at 12:24 PM.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    But for one row, even if the data starts after a gap of empty cells, that set of data will be in continuous cells, right?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Better yet, can you upload a more exhaustive sample on a fileshare site, and post the link here. It will help save development and iteration time.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    attached is a more detailed sample. on the "before and after example" sheet I tried to show how random the data is and how the macro would create new rows.

    Hope this examples it better than my words.

    https://jumbo.iskon.hr/download/0c98...7-972d43115812 just click "preuzmi" to download

  7. #7

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    I've renamed the sheet to ABC

    Code:
    Sub ExcelFox()
    
        Dim lngRow As Long
        Dim lngCol As Long
        Dim lngLoop As Long
    
        With Worksheets("ABC")
            For lngRow = .Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
                For lngCol = .Cells(lngRow, .Columns.Count).End(xlToLeft).Column To 2 Step -1
                    If Not IsEmpty(.Cells(lngRow, lngCol)) And .Cells(lngRow, 1).Value <> 1 Then
                        .Rows(lngRow + 1).Insert
                        .Cells(lngRow + 1, 1).Value = 1
                        .Cells(lngRow + 1, lngCol).Value = .Cells(lngRow, lngCol).Value
                    End If
                Next lngCol
            Next lngRow
        End With
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Please read http://www.excelfox.com/forum/f25/me...-posters-1172/

    Avoid breaking forum guidelines. You may hamper your reputation as a responsible member.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 1
    Last Post: 10-16-2013, 05:06 PM
  2. Replies: 2
    Last Post: 09-18-2013, 12:30 AM
  3. Replies: 2
    Last Post: 02-11-2013, 08:13 PM
  4. Vba Code to find value and paste on certain row
    By jwitte in forum Excel Help
    Replies: 3
    Last Post: 11-28-2012, 08:52 PM
  5. VBA -- Copy/Paste across sheets
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 09-21-2012, 02:07 PM

Posting Permissions

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