Excel - Macro copy data from worksheet to other worksheet

Question

Can someone give me a hand on this excel macro issue i have.

I have a spreadsheet will a quite a few worksheets in it. on the first worksheet in Column A I have loads of numbers. I need a macro to get the first number then go through the worksheets and find that number. Once found then copy the data in cell "M4" of that worksheet, then paste it in the first worksheet in Column D on the same row it got the number from.

So far I have the following code, it kind of work but doesnt do it for all worksheets.

Sub Macro4()



Dim sht As Worksheet

Dim NumberToFind



NumberToFind = ActiveCell.Value



On Error Resume Next



Sheets(3).Select



For Each sht In Sheets

    If Cells.Find(What:=NumberToFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate = 0 Then

    

        ActiveSheet.Next.Select

        

        Else

        

        Range("M4").Select

        

        ActiveCell.Copy

        

        Sheets("Sheet2").Select

        

        Cells.Find(What:=NumberToFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

        

        ActiveCell.Offset(0, 3).Select

        

        ActiveCell.PasteSpecial

        

        ActiveCell.Offset(1, -3).Select

        

        NumberToFind = ActiveCell.Value

        

        Sheets(3).Select

        

        'MsgBox ActiveSheet.Name & "!" & ActiveCell.Address

    

    End If



Next sht

If more info is needed, just let me know.

Answer 1

Hi,

This should work. It will only find the first occurance.

Sub Macro4()
'
' I have a spreadsheet will a quite a few worksheets in it.
' on the first worksheet in Column A I have loads of numbers.
' I need a macro to get the first number then go through the worksheets and find that number.
' Once found then copy the data in cell "M4" of that worksheet,
' then paste it in the first worksheet in Column D on the same row it got the number from.
'
    Dim sht As Worksheet
    Dim NumberToFind
    Dim rngFind As Range
    
    NumberToFind = ActiveCell.Value

    For Each sht In Worksheets
        ' check name otherwise we will find string on first sheet
        If sht.Name <> ActiveSheet.Name Then
            Set rngFind = sht.Cells.Find(What:=NumberToFind, After:=sht.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If rngFind Is Nothing Then
                ' try next sheet
            Else
                sht.Range("M4").Copy ActiveCell.Offset(0, 3)
                Exit For
            End If
        End If
    Next sht
    

End Sub

---

Cheers www.andypope.info

Answer 2

Hi,

This should work. It will only find the first occurance.

Sub
 Macro4()
'

' I have a spreadsheet will a quite a few worksheets in it.

' on the first worksheet in Column A I have loads of numbers.

' I need a macro to get the first number then go through the worksheets and find that number.

' Once found then copy the data in cell "M4" of that worksheet,

' then paste it in the first worksheet in Column D on the same row it got the number from.

'

    Dim
 sht As
 Worksheet
    Dim
 NumberToFind
    Dim
 rngFind As
 Range
    
    NumberToFind = ActiveCell.Value

    For
 Each
 sht In
 Worksheets
        ' check name otherwise we will find string on first sheet

        If
 sht.Name <> ActiveSheet.Name Then

            Set
 rngFind = sht.Cells.Find(What:=NumberToFind, After:=sht.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False
, SearchFormat:=False
)
            If
 rngFind Is
 Nothing
 Then

                ' try next sheet

            Else

                sht.Range("M4"
).Copy ActiveCell.Offset(0, 3)
                Exit
 For

            End
 If

        End
 If

    Next
 sht
    

End
 Sub

---

Cheers www.andypope.info

Thanks for replying, this works a treat at finding the number I run the macro on.
So I just need to put it in a loop to go through the numbers in column A automatically.