Thread: Comparing 2 worksheets and copying values from certain cells into other sheet

Hi,

The following code compares two workbooks and copies certain cell contents from one workbook to the other workbook as and when certain criteria matches in column A (in this case employee names). However is it possible to adapt this code to compare sheets in the same workbook, as I want to accomplish the same goal copying and pasting certain cell values when a match has been determined in column A but across worksheets and not workbooks.

Dim wbMaster As Workbook:   Set wbMaster = ActiveWorkbook
    Dim rngItems As Range:      Set rngItems = wbMaster.ActiveSheet.Range("A1:" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Address)
    Dim wbSource As Workbook, wbPath As String
    wbPath = "C:\Users\Steve\Desktop\Week3.xls"
    
    Application.ScreenUpdating = False
    
    Set wbSource = Workbooks.Open(Filename:=wbPath, UpdateLinks:=False, IgnoreReadOnlyRecommended:=True)
    
    Dim ItemCell As Range, CompareCell As Range, ws As Worksheet, MatchCount As Long:   MatchCount = 0
    For Each ItemCell In rngItems
        Dim ItemFound As Boolean:   ItemFound = False
        For Each ws In wbSource.Worksheets
            For Each CompareCell In ws.Range("A1:" & ws.Range("A" & Rows.Count).End(xlUp).Address)
                If CompareCell.Value = ItemCell.Value Then
                    ItemCell.Offset(0, 8).Value = CompareCell.Offset(0, 4).Value
                    MatchCount = MatchCount + 1
                    ItemFound = True
                    Exit For
                End If
                If ItemFound = True Then Exit For
            Next CompareCell
            If ItemFound = True Then Exit For
        Next ws
    Next ItemCell
    wbSource.Close savechanges:=False
    
    If MatchCount = 0 Then MsgBox "No matches found. Try a different source workbook"
    
    Application.ScreenUpdating = True

End Sub

If you want to reference a worksheet.. you can do it 2 ways...

1 is to use Activeworkbook.Sheets.Count to obtain the total amount of worksheets in a workbook - from there you can determine if you want to work with the 1, 2, 3, 4, etc (up to nth), worksheet.
i.e.

Activeworkbook.Sheets(1), ActiveWorkbook.Sheets(2), etc.

The other method is if you know the worksheet you want to copy to...

ActiveWorkbook.Sheets("copytothissheet").Cells(row, column).Value = ActiveWorkbook.Sheets("firstsheet").Cells(row, column).Value

@Gitbum, @docmed

Per Forum Rules please ensure VBA is posted within CODE tags (this includes one liners & pseudo-code)

I've modified posts on your respective behalf.