If Then Statement to Copy and Paste from 1 Table to Another

[auslegung]

I need the below if statement to be in a loop that goes through all of Table1. However, I am new to programming in general, and brand new to VBA syntax. Can someone please write the code, or point me to a resource that will quickly teach me how to write this specific code?

If K is equal to AM {
Copy AP:AR and paste to AC:AE
}

 

[Wookiee]

This code should work:

Sub CopyLoop()
'Assumes That Table1 Contains Headers In Row 1

'Declare Variables
Dim lngRow As Long, lngLoop As Long

'Set Variable To Count Number Of Rows In Table1
lngRow = Range("Table1").Rows.Count + 1

'Loop Through Table, Comparing Value In K to Value In AM
'And Copy AP:AR To AC:AE In Same Row If Matched
For lngLoop = 2 To lngRow
    If Range("K" & lngLoop).Value = Range("AM" & lngLoop) Then _
        Range("AP" & lngLoop & ":AR" & lngLoop).Copy Range("AC" & lngLoop)
Next lngLoop

End Sub

 

[auslegung]

Thanks for the quick response! It kinda works, but it's only pasting when the text in "K" changes. Does that make sense?

Also, is it possible to use the column name instead of "K"? Column K is named "FGC Coordinator Agency", so could I use [@[FGC Coordinator Agency]] instead of "K"? There's a possibility that the columns will be moved over time, and this would be better I think.

 

[Wookiee]

I'm sorry for my delay in responding; I was unable to find any sources which provided an example of referencing cells in a table using the column header.

 

[Wookiee]

As much as I find the term cliché, I guess I just needed to think outside the box. I figured out a way to reference the column by using a string variable. This way, if the column labeled [FGC Coordinator Agency] moves in your table, all you have to do is change the value of strFGC in the macro. Does this work better for you?

Sub CopyLoop_Zwei()
'Assumes That Table1 Contains Headers In Row 1

'Declare Variables
Dim lngRow As Long, lngLoop As Long
Const strFGC As String = "K"

'Set Variable To Count Number Of Rows In Table1
lngRow = Range("Table1").Rows.Count + 1

'Loop Through Table, Comparing Value In K to Value In AM
'And Copy AP:AR To AC:AE In Same Row If Matched
For lngLoop = 2 To lngRow
    If Range(strFGC & lngLoop).Value = Range("AM" & lngLoop).Value Then _
        Range("AP" & lngLoop & ":AR" & lngLoop).Copy Range("AC" & lngLoop)
Next lngLoop

End Sub

 

[auslegung]

I'm still having the issue where only 1 line gets pasted, and the others are blank until there's a different value in K, at which point that line gets pasted, etc. Do you know what I'm talking about?

I'm not super worried about using the columm header, I can always edit it in the Macro, I just figured it would be more built-in since it could be more stable.

 

[Wookiee]

I'm still having the issue where only 1 line gets pasted, and the others are blank until there's a different value in K, at which point that line gets pasted, etc. Do you know what I'm talking about?

Auslegung, I think I understand what you're saying now. I was assuming that you wanted to only copy the range from columns AC:AE if the values of K and AM were the same, row by row.

Am I correct in understanding that you want the code to copy the values in columns AC:AE down from the cells immediately above them if they're blank? If that is the case, please try this code:

Sub CopyLoop_Drei()
'Assumes That Table1 Contains Headers In Row 1

'Declare Variables
Dim lngRow As Long, lngLoop As Long

'Set Variable To Count Number Of Rows In Table1
lngRow = Range("Table1").Rows.Count + 1

'Loop Through Table, Comparing Value In K to Value In AM
'And Copy AP:AR To AC:AE In Same Row If Matched
For lngLoop = 2 To lngRow
    If Range("K" & lngLoop).Value = Range("AM" & lngLoop) Then _
        Range("AP" & lngLoop & ":AR" & lngLoop).Copy Range("AC" & lngLoop)
Next lngLoop

'Loops Through Rows Again, Filling In AC:AE With Values In Cells Above If Blank
For lngLoop = 2 To lngRow
    If IsEmpty(Range("AC" & lngLoop)) Then
        Range("AC" & lngLoop & ":AE" & lngLoop).Value = _
        Range("AC" & lngLoop - 1 & ":AE" & lngLoop - 1).Value
    End If
Next lngLoop

End Sub

 

[auslegung]

This code isn't doing what I need, either. I will try to explain again what I need.

If K2 is equal to AN2, copy AQ2:AS2 and paste it to AC2:AE2.
If K3 is equal to AN3, copy AQ3:AS3 and paste it to AC3:AE3.
If K4 is equal to AN4, copy AQ4:AS4 and paste it to AC4:AE4.
If K5 is equal to AN5, copy AQ5:AS5 and paste it to AC5:AE5.
...

If K1537 is equal to AN1537, copy AQ1537:AS1537 and paste it to AC1537:AE1537.


The first code you provided did the following:
</SPAN>
If K2 is equal to AN2, copy AQ2:AS2 and paste it to AC2:AE2.
(K2 is equal to AN2, so copy AQ2:AS2 and paste it to AC2:AE2).
(K3 is equal to K2, so skip)
(K4 is equal to K2, so skip)
...
(K27 is equal to AN27, so copy AQ27:AS27 and paste it to AC27:AE27)
(K28 is equal to K27, so skip)
(K29 is equal to K27, so skip)
...

This left me with only a couple of cells filled in, but at least they were correct.

The second code you provided me did the following:

If K98 is equal to AN98, copy AQ98:AS98 and paste it to AC98:AE98.</SPAN>
(K98 is equal to AN98, so copy AQ98:AS98 and paste it to AC98:AE98).</SPAN>
and then it pasted the same values down the entire table, filling in hundreds of cells with AQ98:AS98, and most of them were wrong.

I really do not understand why the first code pasted only once, then skipped all rows until it found a different value in K, pasted only once, skipped all rows until it found a different value in K, etc.

 

[Wookiee]

Auslegung, I'm scratching my head over this one. When I put the first macro together, I created a table with dummy information to test that the macro performed as I intended. Table1 was in cells K1:AR18 and on every line where the value in column K matched what appeared in column AM, it copied the cells from AP:AR to AC:AE in the same row, regardless of what the previous value in K was.

My primary theory is that it might have to do with the formatting of your cells. If you can post an example file, it might help me figure out why the macro isn't working as expected. Aside from that, there was one minor correction I found that I should have caught before posting the code. I had intended to compare Range.Value to Range.Value instead of Range.Value to Range. I don't know if that will fix the issue you're experiencing, but I figure it's worth a try.

Sub CopyLoop_Revisited()
'Assumes That Table1 Contains Headers In Row 1

'Declare Variables
Dim lngRow As Long, lngLoop As Long

'Set Variable To Count Number Of Rows In Table1
lngRow = Range("Table1").Rows.Count + 1

'Loop Through Table, Comparing Value In K to Value In AM
'And Copy AP:AR To AC:AE In Same Row If Matched
For lngLoop = 2 To lngRow
    If Range("K" & lngLoop).Value = Range("AM" & lngLoop).Value Then _
        Range("AP" & lngLoop & ":AR" & lngLoop).Copy Range("AC" & lngLoop)
Next lngLoop

End Sub

 

[Wookiee]

Auslegung, I'm scratching my head over this one. When I put the first macro together, I created a table with dummy information to test that the macro performed as I intended. Table1 was in cells K1:AR18 and on every line where the value in column K matched what appeared in column AM, it copied the cells from AP:AR to AC:AE in the same row, regardless of what the previous value in K was.

My primary theory is that it might have to do with the formatting of your cells. If you can post an example file, it might help me figure out why the macro isn't working as expected. Aside from that, there was one minor correction I found that I should have caught before posting the code. I had intended to compare Range.Value to Range.Value instead of Range.Value to Range. I don't know if that will fix the issue you're experiencing, but I figure it's worth a try.

Sub CopyLoop_Revisited()
'Assumes That Table1 Contains Headers In Row 1

'Declare Variables
Dim lngRow As Long, lngLoop As Long

'Set Variable To Count Number Of Rows In Table1
lngRow = Range("Table1").Rows.Count + 1

'Loop Through Table, Comparing Value In K to Value In AM
'And Copy AP:AR To AC:AE In Same Row If Matched
For lngLoop = 2 To lngRow
    If Range("K" & lngLoop).Value = Range("AM" & lngLoop).Value Then _
        Range("AP" & lngLoop & ":AR" & lngLoop).Copy Range("AC" & lngLoop)
Next lngLoop

End Sub