none
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.
    Wednesday, May 27, 2009 2:17 AM

All replies

  • 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
    Wednesday, May 27, 2009 7:39 AM
  • 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.

    Wednesday, May 27, 2009 9:11 PM