VBA code - Copy pasting cells from sheet1 to sheet2

J S A

New Member
Joined
Mar 7, 2023
Messages
14
Office Version
  1. 365
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
 
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!
 

Attachments

  • vba script outcome.JPG
    vba script outcome.JPG
    186 KB · Views: 6
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What address is printed when you run this?

VBA Code:
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.
 
Upvote 0
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

VBA Code:
Dim DestinationHeadersRNG As Range: Set DestinationHeadersRNG = wsDESTINATION.Range("A2").CurrentRegion.Rows(2)
 
Upvote 0
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

VBA Code:
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.
 
Upvote 0
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

VBA Code:
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!!
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top