More macro help needed

[AFST]

Alright folks,

I've done some looking around and there's a lot of similar problems to mine and they all seem to be solved with a macro. Way out of my element there so I need a big hand. Here it goes.

I have a master sheet with a cell (call it A1) that has a drop down list where I can select the name of a person. Now based on whatever name I pick I want the program to check all of my other worksheets (in this case each worksheet is a month and ideally the code is written so that if I create a new sheet it will also check that one) for the name and pull up all the data that is in the same row as the name.

Now I'm not sure if this is bad news or not but all of my columns are not single cells, rather merged cells. I'm way too far along now to go back and change it.

All of the monthly worksheets are the exact same format and I think it would look best if the returned values I'm looking for are also in the same format.

The name could turn up in any one of 8 columns but it would only be in one row at a time. The columns it needs to check on each sheet are (AR18:AU118), (AV18:AY118), (AZ18:BC118), (BD18:BG118), (BH18:BK118), (BL18:BO118), (BP18:BS118), (BT18:BW118)

So for example if the program searched and found a name in that second column I listed in the 20th row it duplicates all in info found on the 20th row of that sheet, and if the next occurrence was on the same sheet in row 32 then it duplicates row 32 but places it on the master sheet in the row immediately below the duplicate of the first occurrence, etc. And then to make it more difficult it has to check and combine data from multiple worksheets.

I hope I've managed to make this somewhat clear. Please help!

 

[iliace]

This should get you started. It takes the value in A1 on a sheet called "master sheet", looks for that name on all the other sheets, and retrieves the data like you specified, starting in row 3 of the "master sheet".

Public Sub FindName()
  Const strMasterSheet As String = "master sheet"

  Dim wshMaster As Excel.Worksheet
  Dim wsh As Excel.Worksheet
  Dim strName As String
  Dim lResult As Long
  
  Dim firstFind As String
  
  Dim rngFind As Excel.Range
  
  Set wshMaster = ThisWorkbook.Worksheets(strMasterSheet)
  strName = wshMaster.Range("A1").Value
  
  For Each wsh In ThisWorkbook.Worksheets
    firstFind = vbNullString
    
    If StrComp(wsh.Name, strMasterSheet) <> 0 Then
      On Error Resume Next
      With wsh.Range("AR18:BW118")
        Set rngFind = .Find(strName)
        If Not rngFind Is Nothing Then
          firstFind = rngFind.Address
          Do
            lResult = lResult + 1
            Intersect(rngFind.EntireRow, wsh.UsedRange).Copy wshMaster.Cells(2, 1).Offset(lResult, 0)
            Set rngFind = .FindNext
          Loop While Not rngFind Is Nothing And rngFind.Address <> firstFind
        End If
      End With
    End If
  Next wsh
End Sub