VBA code - Copy pasting cells from sheet1 to sheet2

[J S A]

Hi, Need your help. I am learning to write some VBA code with criteria matching the header required to bring the data from one sheet to another.
Below is the code which runs successfully to an extent where the vba fails to bring the last row cell from the respective column and thereby failing the loop too.

Dim head_count As Integer
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("PO lines data")
Set ws2 = ThisWorkbook.Sheets("Cleansed data")

ws2.Activate
head_count = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlToRight)))

ws1.Activate
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
row_count = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlDown)))

For i = 2 To head_count

j = 1

Do While j <= col_count

If ws2.Cells(2, i) = ws1.Cells(1, j).Text Then

ws1.Range(Cells(1, j), Cells(row_count, j)).copy
ws2.Cells(2, i).PasteSpecial xlPasteValues
Application.CutCopyMode = False
j = col_count

End If

j = j + 1

Loop

Next i

 

[J S A]

I was hoping that would be it. I'm using my cell phone to this conversation, so I can't do better now, without a computer. Can you upload your workbook or worksheets and vba here with a few lines of dump example data?

Since i am using office laptop unable to share the data through mini-sheet upload. However, I am sharing the image where I used the VBA codes provided by you and the outcome is as expected.
However, while using the same VBA code in my working file it is not working for some reason unknown!

 

[Tupe77]

What address is printed when you run this?

Debug.print Worksheets("Cleansed data").range("A2").currentregion.address

I believe the button above the headers is read into the region, so the currentregion definition moves up one row. And of course it won't find any headlines there.

 

[Tupe77]

If the address starts in cell A1, as I suspect, then changing this row may solve the problem. But i haven't tested it. Only the row has been changed from 1 to 2

Dim DestinationHeadersRNG As Range: Set DestinationHeadersRNG = wsDESTINATION.Range("A2").CurrentRegion.Rows(2)

 

[J S A]

If the address starts in cell A1, as I suspect, then changing this row may solve the problem. But i haven't tested it. Only the row has been changed from 1 to 2

Dim DestinationHeadersRNG As Range: Set DestinationHeadersRNG = wsDESTINATION.Range("A2").CurrentRegion.Rows(2)

If that was a challenge then the VBA script would not have worked in a non-working sheet which was replicated to meet the criteria between headers in both destination and source file. The image that i shared with you was the outcome in a non-working file which is perfect.

 

[J S A]

If the address starts in cell A1, as I suspect, then changing this row may solve the problem. But i haven't tested it. Only the row has been changed from 1 to 2

Dim DestinationHeadersRNG As Range: Set DestinationHeadersRNG = wsDESTINATION.Range("A2").CurrentRegion.Rows(2)

It worked by changing the current region from 1 to 2. Thank you for the help!!