CODEWITHSUNDEEP
vbaexcel
barciewicz
barciewicz

Reputation: 3813

Embedded "for each" loop condition is not true after first iteration

The following macro is made for copying content from under column headers in one sheet (with variable column order, but same column names) to another sheet (one column next to another for now). The problem is that after first iteration of the embedded For Each loop, the condition "cell = header" is no longer true, because "Next cell" was obviously not yet executed. Is there any workaround for this or do I have to rewrite completely?

Sub CopyContentBelowHeadersToAnotherSheet ()

Dim headers As Range
Dim cell As Variant
Dim header As Variant
Dim CopiedHeaders As Variant
Dim is as Variant



Set headers = Workbooks("GL audit template 3.0.xlsm").Worksheets ("Sheet3").Range("A1:Z1")
CopiedHeaders = Array("DocumentNo", "G/L", "Type", "Tx", "Text", "BusA", "Doc. Date", "Amount in local cur.")
i = 1

For Each cell In headers
    For Each header In CopiedHeaders
         If cell = header Then ' this is no longer true after first iteration of this loop 
          cell.Offset(1, 0).Activate 
          Range(ActiveCell, ActiveCell.End(xlDown)).Copy
          Workbooks("GL audit template 3.0.xlsm").Worksheets("Sheet1").Activate
          Cells(2, i).Activate
          ActiveSheet.Paste
          i = i + 1
        End If
    Next header
Next cell

End Sub

Upvotes: 1

Views: 55

Answers (1)

iDevlop
iDevlop

Reputation: 25272

Please get rid of those slow and useless Activate and ActiveCell!
I have not tested, but this should work better.

For Each cell In headers
    For Each header In CopiedHeaders
         If cell = header Then ' this is no longer true after first iteration of this loop 
          With cell
             Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Copy
             Workbooks("GL audit template 3.0.xlsm").Worksheets("Sheet1").Cells(2, i).Paste
          End with
          i = i + 1    'edited
        End If
    Next header
Next cell

Upvotes: 1

Related Questions