VBA code to copy and paste row based on certain criteria

**T0NT0** · 10-30-2013, 06:56 PM

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

**Excel Fox** · 10-30-2013, 10:43 PM

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?

**T0NT0** · 10-30-2013, 11:20 PM

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

**Excel Fox** · 10-30-2013, 11:29 PM

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?

**Excel Fox** · 10-30-2013, 11:30 PM

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.

**T0NT0** · 10-30-2013, 11:40 PM

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

**rollis13** · 10-30-2013, 11:50 PM

Cross-post:
VBA code to copy and paste row based on certain criteria
VBA code to copy and paste row based on certain criteria
VBA code to copy and paste row based on certain criteria. need help with Paste part

**Excel Fox** · 10-31-2013, 12:30 AM

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

**Excel Fox** · 10-31-2013, 12:38 AM

Please read http://www.excelfox.com/forum/f25/me...-posters-1172/

Avoid breaking forum guidelines. You may hamper your reputation as a responsible member.