Copy paste loop with a nested loop and duplicate row

[nekme]

Hi all,

I'm looking to run a series of copy pasting and duplicate row macro.

I need to:
(1) Copy paste y2:z2 to ab2:ac2
(2) Autofill ab2:ac2 to last row (data in column to the right)
(3) Copy paste ac2:ae* (eg ae18) to another sheet (a2:c*)

Then:

(4) Copy paste y3:z3 (until last row of data) to ab2:ac2
(5) Autofill ab2:ac2 to last row (data in column to the right)
(6) Copy paste ac2:ae* (eg ae18*) to the row after the last row used in step (3).

Then:

(7) In the other sheet, i'd have a2:c*. I need to duplicate cell a & b X times according to the number that's found in c

E.g.

A B C
alan dennis 3
alan ben 1
alan alice 2

would be

A B C
alan dennis 3
alan dennis 3
alan dennis 3
alan ben 1
alan alice 2
alan alice 2

I've been at this for quite some time and have a basic understanding of the techniques that should be used (such as for each loop (with a nested loop), find last row of columns) etc but I can't find anything suitable and I can't code from scratch (not to mention I get very confused by some of the codes). For the codes that are posted, can you also explain the solution line by line so I have a good understanding of the concept for future reference?

TIA!

 

[Joe4]

Welcome to the Board!

For new coders, the Macro Recorder can become a very good friend!
You can get a lot of the code you need by using the Macro Recorder, and record yourself performing the steps you outlined in steps 1-6 manually.
Then, you can stop the Macro Recorder, and do a little clean-up on the code and make it more dynamic.

Here are a few tips that you can use to make that code more dynamic:

To find the last row with data in column A, you can use this VBA formula:
lastRow = Cells(Rows.Count,"A").End(xlUp).Row

To find the last column with data in row 1, you can use this VBA formula (note that this returns the column number, not the column letter):
lastCol = Cells(1,Columns.Count).End(xlToLeft).Column

Lastly, regarding your last item, whenever you are inserting or deleting rows in a range using a loop, it is usually better to loop backwards, from the bottom to the top, so you aren't affecting and moving the rows you haven't hit yet. So, that loop start may look like"
For r = lastRow to 1 Step -1

Armed with these tips & tricks, why not try it out and see how far you get? Then, if you get stuck, post what you have created here so far, and we can help you work through it.
You will learn a lot more by working through it this way rather than just having us do all of it for you.

 

[nekme]

Hi Joe4,

Thanks for the reply! I spent some time fiddling with macros but the codes were so verbose to a point it affected my mental health . Spent the last 2 days studying and practising loops and miraculously I was able to patch something up that works for me.

Dim stylelastrow As Long
stylelastrow = Cells(Rows.Count, "y").End(xlUp).Row


Dim helperlastrow As Long
helperlastrow = Cells(Rows.Count, "ad").End(xlUp).Row
  
For i = 2 To stylelastrow
    Range("ab2") = Cells(i, "y")
    Range("ac2") = Cells(i, "z")
    Range("ac2").AutoFill Destination:=Range("ac2:ac" & helperlastrow), Type:=xlFillCopy
           
    ActiveSheet.Range("$AC$1:$AE$18").AutoFilter Field:=3, Criteria1:="<>0"
    Range("AC2:AE18").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Sheets("Output").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      
Next i

Got a couple of questions about the code and best practices.

1. How do I combine these 2 lines? I tried a few ways but they didn't work.

Range("ab2") = Cells(i, "y")
Range("ac2") = Cells(i, "z")

2. When it comes to dynamic references (not sure if this is the right description but generally problems that involve dynamic ranges (e.g. 10 rows 10 columns) rather than defined ranges (e.g. A1:A10), I have a very difficult time breaking down the problem into smaller manageable elements. For instance, the code below:

    Dim xRow As Long
    Dim VInSertNum As Variant
    xRow = 1
        Do While (Cells(xRow, "A") <> "")
        VInSertNum = Cells(xRow, "C")
        If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
           Range(Cells(xRow, "A"), Cells(xRow, "C")).Copy
           Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "C")).Select
           Selection.Insert Shift:=xlDown
           xRow = xRow + VInSertNum - 1
        End If
        xRow = xRow + 1
    Loop

This bit "Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "C")).Select" alone makes me spend a long time trying to put together. What is a good resource to learn this process?

 

[Joe4]

How do I combine these 2 lines? I tried a few ways but they didn't work.

Range("ab2") = Cells(i, "y")
Range("ac2") = Cells(i, "z")

What do you mean by combine these two lines?
They look like two different/distinct lines?
Or are you just asking about looping through ranges and performing the same steps on each?

If so, note that with the Cells(row,column) referencing you can use letters or number for column references. This comes in quite handy when working with loops.
column "AB" = column 28
column "Y" = column 25
etc.

So if you wanted to write the previous commands in a loop, you could use:

Dim c as Long
For c = 25 to 26
    Cells(2,c+3) = Cells(i,c)
Next c

I probably wouldn't use a loop like this if it was just two cells that we were updating. But if it was more than that and they follow this pattern, then it would make sense to do so.

Regarding your second question, a user here (hiker95) put together a great list of VBA resources.
See: https://www.mrexcel.com/forum/excel...e-template-excel-spreadsheet.html#post4972842

 

[nekme]

Great thanks! A question on using counters like:

1) xRow = xRow + 1
2) colCRow = colCRow + 1 (https://www.mrexcel.com/forum/excel-questions/793682-using-vba-concatenate-2-lists.html)

Counters were used in 2 different types of loops. The first one was a Do While loop whereas the second one was used in a For Each loop.

What's the purpose of using a counter when loops already have mechanisms to address increments? e.g. offset or built in values (Cells(2,c+3) = Cells(i,c))

 

[Joe4]

You usually don't need counters in For/Next loops, as you are telling it what values to cycle through.
You usually use them in Do/While loops, as you are not telling it explicitly what values to loop through, you tell it to continue looping until you have reached a certain value or condition.

What's the purpose of using a counter when loops already have mechanisms to address increments? e.g. offset or built in values (Cells(2,c+3) = Cells(i,c))

The "c+3" is NOT an incrementer in this case, but rather an offset.