VBA question - Copy/Paste/Loop

[QueenOfHearts]

I am brand new to VBA, so please go easy on me... I have spent several hours learning about VBA, and playing around with different ways to make my looping problem work, but I am now at a complete loss.

I am trying to create a copy/paste macro to loop through my worksheet. I need L3:Q3 to be copied and pasted into F4:K4, then have the same action performed on every OTHER row down, 200 times. My code is copying and pasting but in the wrong columns. It is copying and pasting for every other row, but is copying columns DBD:DBI and pasting into columns DAX:DBC. This is completely out of my data range. My current code is below. Any help on how to fix this would be most appreciated!

Dim x As Integer
For x = 3 To 200


ActiveCell.Offset(2, 11).Range("A1:F1").Select
Selection.Copy
ActiveCell.Offset(1, -6).Range("A1").Select
ActiveSheet.Paste

Next x


End Sub

Thank you!

 

[Joe4]

Welcome to the Board!

So, L3:Q3 would be pasted to F4:K4.
Your next one would be L5:Q5, right? Would that be posted to F6:K6, or F5:K5?

 

[QueenOfHearts]

You are correct. My next one would be L5:Q5. It would need to be pasted in F6:K6.

 

[Joe4]

Try this:

    Dim x As Integer
    
    For x = 3 To 401 Step 2
        Range(Cells(x, "L"), Cells(x, "Q")).Copy Destination:=Range(Cells(x + 1, "F"), Cells(x + 1, "K"))
    Next x

 

[QueenOfHearts]

So sorry it took so long to respond to your suggestion. IT WORKED PERFECTLY! Thank you so much. If you have the opportunity to break down the code you gave me, so I can understand for future reference, would love that. Thanks again!

 

[Joe4]

Sure.

First, let's look at the loop.
For x = 3 To 401 Step 2

You said that you wanted to start in row 3, skipping every other row, for 200 loops.
So that would be rows 3 to 401.
If we want to "skip" every other number, the "Step" command allows us to do that. That tells us to add 2 to the loop each time.
Likewise, you can also loop backwards, if you like, by using a negative number. You often see this when people use loops to delete rows.

Now, it is important to understand the various ways of referring to ranges.
I am sure that you probably have seen the Range reference, like Range("A1").
Another one is Cells. the format is Cells(row, column).
The nice thing about that is for the column reference, you can use either the letter representation (i.e. "A") or numerical (i.e. 1)
So, the equivalent of Range("A1") would be:
Cells(1,"A")
or
Cells(1,1)

Now, in referencing a multiple-cell range, you are probably familiar with:
Range("L3:Q3")
Incorporating the cells format above, we could also write that like:
Range(Cells(3,"L"), Cells(3,"Q"))

So, we just incorporated all that logic into the statement:
Range(...).Copy Destination:=Range(...)

Does that make sense?
If you have any other specific questions on it, feel free to ask them there.

 

[QueenOfHearts]

Makes perfect sense. Thank you so much for giving me a clear explanation. Much appreciated!

 

[Joe4]

You are welcome!

I always like it when people are not simply looking for answers, but also want to learn and understand things.