Search workbook for matching detail and copy/paste the row/column content.

Hi,

Welcome the World of Excel Forum and enjoy .

Please provide the sample workbook its easy to solve.

Thanks - Naveed

Bump - any additional information required, please ask.

Hi,

I want to know one thing is that course tabs will increase day by day or else that three tabs only.

Naveed Raza - There will be one or two extra tab added during a year only.

so total max to max how many tabs will there so that i can update the formula only macro will not required

There will never be more than 6 tabs, though the number of rows compared with the sample sheet will be higher. There could be as many as 700 rows on a tab.

GB,
See if this gives you a good start.
Please note that I have also highlighted the common cells in both track and other sheets. Let me know if you wish to remove them

Sub find_copy()
Dim c2 As Range, c As Range
With Sheets("Track")
    .Range("C2:C" & .Rows.Count - 1).Clear
    For Each c2 In .Range("B2:B" & .Cells(Rows.Count, 1).End(xlUp).Row)
        For Each ws In Worksheets
            If ws.Name Like "Course*#" Then
            Set c = ws.Columns(2).Find(c2, , xlValues, xlWhole)
                If Not c Is Nothing Then
                 c.EntireRow.Interior.ColorIndex = 3
                 c2.EntireRow.Interior.ColorIndex = 3
                c.Offset(, 1).Copy Sheets("Track").Range("C" & Rows.Count).End(xlUp).Offset(1)
                End If
        End If
        Next ws
   Next c2
 End With
End Sub

Hi,

I have updated the formula in Track Tab.

Please have review and let me know if u hv any question/queries

Thanks - Naveed

Naveed Raza - Unfortunately the version of Excel I am using at work only allows a max of seven levels of nesting in a forumla, so it is not able to open the formula. But thank you very much for your assiatnce.

AB33 - That looks like it fits exactly what i need it to do, though highlighting the fields is something that wouldn't work (I hadn't mentioned that cells are colour coded on the sheets separately to represent something else, so I will need to remove that one option). Thankyou very much for your help

I am glad to know it has worked for you!

I have just noticed one problem that I may have. Where someone appears on the Track sheet but does not appear on a Course sheet it would mean that the copy and pasted information would not be in the same row afterwards.

Is there an easy way to add to the macro that where someone appears on the Track sheet but is not found on the Course sheets to output to the relevant column/row "Not Found" so that the information found on people below that name have their data in the same row?

GB,
I was aware the fact that the rows found in each sheet do not match with the row numbers in the Trackt sheet. I am still trying to get around the problem. The code copies when it finds match in to the next empty cells in track column C, instead of aliening with the row number. In the mean time, I have added a new line, and see if this helps.

Sub find_copy()
Dim c2 As Range, c As Range
With Sheets("Track")
    .Range("C2:C" & .Rows.Count - 1).Clear
    For Each c2 In .Range("B2:B" & .Cells(Rows.Count, 1).End(xlUp).Row)
        For Each ws In Worksheets
            If ws.Name Like "Course*#" Then
            Set c = ws.Columns(2).Find(c2, , xlValues, xlWhole)
                If Not c Is Nothing Then
                 c.Offset(, 3) = "Found"
                 c2.Offset(, 3) = "Found"
                c.Offset(, 1).Copy Sheets("Track").Range("C" & Rows.Count).End(xlUp).Offset(1)
                End If
        End If
        Next ws
   Next c2
 End With
End Sub

Mike!
It does indeed! Thanks again for your timely intervention.

Now the found rows match with the copied match on the track sheet. I hope this will satisfay the OP.

Sub find_copy()
Dim c2 As Range, c As Range
With Sheets("Track")
    .Range("C2:E" & .Rows.Count - 1).Clear
    For Each c2 In .Range("B2:B" & .Cells(Rows.Count, 1).End(xlUp).Row)
        For Each ws In Worksheets
            If ws.Name Like "Course*#" Then
            Set c = ws.Columns(2).Find(c2, , xlValues, xlWhole)
                If Not c Is Nothing Then
                 c.Offset(, 3) = "Found"
                 c2.Offset(, 3) = "Found"
                c.Offset(, 1).Copy Sheets("Track").Range(c2.Address).Offset(, 1)
                End If
        End If
        Next ws
   Next c2
 End With
End Sub

AB33 I ran that on my test document, first look it ran beautifully, looking very hopeful that you and mike7952 have solved my problem. I'll adapt it for my main sheet and see how it runs. Again thank you very much for your time, effort and overall assistance. Much appreciated.

mike7952 that line looks to solve the problem on my test sheet. Thank you very much.