Newbie needs help.

[Steoin]

Hey, complete novice here so don´t know how to begin looking for an answer.

Basically I have a workbook with a number of sheets and in the first sheet I have the complete dataset. I have a list of names in the A column of each sheet and I want to use some function to go through the list of names and find them in the first sheet and copy all the info from the rows beside the name.

For example the 15th name (A15) in sheet 2 was in A182 in the first sheet and I wanted to copy all the info from B182 to IR182 and paste it in the second sheet Column b15 to IR15.

Any help in terms of what to search for etc much appreciated.
Also if anyone could recommend any courses/series or books etc that would give me the required knowledge to carry out those sort of tasks that´d be great as I could be using Excel a fair amount in the future.

 

[mumps]

Try this macro. I assumed that the name of the sheet containing the complete dataset is named "Sheet1". Change this name in the code to suit your needs.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim name As Range
    Dim foundName As Range
    For Each ws In Sheets
        If ws.name <> "Sheet1" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For Each name In ws.Range("A1:A" & LastRow)
                Set foundName = Sheets("Sheet1").Range("A:A").Find(name, LookIn:=xlValues, lookat:=xlWhole)
                If Not foundName Is Nothing Then
                    ws.Range("B" & name.Row & ":IR" & name.Row).Copy Sheets("Sheet1").Cells(foundName.Row, 2)
                End If
            Next name
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub