Macro to CopyRange not working

[Lorr81]

Hi,

I have been using this macro for years but it is not working, I am trying to combine two sheets based on Text Field A in both sheets.

Sub CopyRange()

    Application.ScreenUpdating = False

    Dim LastRow As Long

    LastRow = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Dim ID As Range

    Dim foundID As Range

    For Each ID In Sheets("Sheet2").Range("A2:A" & LastRow)

        Set foundID = Sheets("Sheet1").Range("A:A").Find(ID, LookIn:=xlValues, LookAt:=xlWhole)

        If Not foundID Is Nothing Then

            Sheets("Sheet2").Range("B" & ID.Row & ":H" & ID.Row).Copy Sheets("Sheet1").Range("I" & foundID.Row)

        End If

    Next ID

    Application.ScreenUpdating = True

End Sub

What am I missing?

Thanks,
Lorr

 

[Peter_SSs]

When posting code, please use code tags to preserve the indentation formatting. I have fixed that in your post above. My signature block below has more help about this.

The code appears to be working for me. In what way is it failing for you? Errors, Crashes, data moved to wrong rows etc?

 

[Joe4]

I find that most of the time, when you encounter a problem where something that was working for years suddenly stopped working, and you haven't changed the code any, the issue usually lies in the data. Something might have changed with your data structure. That is the first thing I would check.

If you can find an old file that worked, try running that again. If it still works, then that definitely points to something different about the data file you are using that is not working correctly.
I like to step through my code line-by-line, and watch what is happening on the sheet. Often times, the problem, will become evident when you see exactly what is happening each step of the way.

 

[Lorr81]

When posting code, please use code tags to preserve the indentation formatting. I have fixed that in your post above. My signature block below has more help about this.

The code appears to be working for me. In what way is it failing for you? Errors, Crashes, data moved to wrong rows etc?

Thank you,
It is just taking the headings over and nothing else, I am using a name in column A to compare could that be the problem? have also just opened a new workbook and entered the code again and it is now doing nothing.

 

[Lorr81]

I find that most of the time, when you encounter a problem where something that was working for years suddenly stopped working, and you haven't changed the code any, the issue usually lies in the data. Something might have changed with your data structure. That is the first thing I would check.

If you can find an old file that worked, try running that again. If it still works, then that definitely points to something different about the data file you are using that is not working correctly.
I like to step through my code line-by-line, and watch what is happening on the sheet. Often times, the problem, will become evident when you see exactly what is happening each step of the way.

Hi Joe,

I have checked and the data seems fine, all it is doing is copying the headers from sheet two.

Thank You

 

[Peter_SSs]

Could you post small samples of dummy data from each sheet with XL2BB so that we can test with your data?

Also, as Joe suggested, try stepping through the code a line at a time (put your cursor in the code and press F8 to advance line-by-line and see what is happening)

 

[Joe4]

I have checked and the data seems fine, all it is doing is copying the headers from sheet two.

Did you try re-running on an old file that you know worked?

If you did, and the old file works and the new file did not, something changed. You just have to find what that is (it might not be the actual data itself; it could be something like the name of the Sheet, or protected ranges, etc). The method I mentioned of stepping through the files may help you identify that.

 

[Lorr81]

Did you try re-running on an old file that you know worked?

If you did, and the old file works and the new file did not, something changed. You just have to find what that is (it might not be the actual data itself; it could be something like the name of the Sheet, or protected ranges, etc). The method I mentioned of stepping through the files may help you identify that.

you were correct it was a data issue, I ran it on the old sheet and all was fine. there was a space in front of all the data in column A in one the sheets,

Thank You

 

[Joe4]

You are welcome.