Find Data & Paste in pervious column on multiple sheets

**excelgrrl** · 12-14-2008, 04:30 PM

I have a workbook that has 397 sheets. On many of the sheets (but not all) they have some of the same data. On sheet 1 cell B5 is ContactID, Sheet 15 cell B32 is also ContactID. Sheet 1 A5 needs to reflect Contact ID and Sheet 15 A32 needs to reflect Contact ID. I need to find all sheets that contain the data "ContactID" and place (paste?) Contact ID in the same row, but in Column A.

Right now I am doing a Find all and clicking through the sheets 1 at a time (some of these finds though have 30+ sheets).

Is there a macro that I can use for this same funcion?

**Richard Buttrey** · 12-14-2008, 04:46 PM

Hi,

A couple of Qs.

What does B5 actually contain? I'm guessing it's a piece of data that represents a Contact ID code/name not the text 'Contact ID'. But in which case how should a macro recognise it's a Contact ID and not another piece of data.
Or is it simply the text 'Contact ID'

Is only column B involved or could the Contact ID be in any column?

Are there more than one ID in a sheet?

Rgds

**excelgrrl** · 12-14-2008, 05:03 PM

The data is only in column B and the words ContactID is only text.

**shg** · 12-14-2008, 05:43 PM

Just paste the word "ContactID" in Sheet1 column A in the same row in which it appears on every other sheet?

And the answers to Richard's other questions?

**excelgrrl** · 12-14-2008, 06:19 PM

The text "ContactID" that I mentioned is not on the same row in every sheet (B5 on Sheet 1 and B32 on Sheet 15, etc).

The text "ContactID" is only text, nothing more.

There are several different 'text' strings that I will want to search for in column B of every sheet and then paste another string of text in column A.

I am wondering if there is a macro that I can use that will be similar to the find function. When I run the macro it will pop open a box that I will type text to search for and type text that it will paste. B1 on Sheet 1 has CompanyID and Company ID is also located on: Sheet 15 B26, Sheet 32 B12, etc. I want to paste "Company Name" in A1 Sheet 1, A26 Sheet 15 and A12 Sheet 32.

**shg** · 12-14-2008, 06:31 PM

The question was, does ContactID appear more than once on any of the other sheets.

**excelgrrl** · 12-14-2008, 06:36 PM

Sorry. No it doesn't.

**shg** · 12-14-2008, 06:54 PM

Maybe ...

Sub x()
    Dim sFind   As String
    Dim iWks    As Long
    Dim rFind   As Range

    sFind = InputBox("Find what?")
    If Len(sFind) = 0 Then Exit Sub

    For iWks = 2 To Worksheets.Count
        Set rFind = Worksheets(iWks).Cells.Find(What:=sFind, _
                                                LookIn:=xlValues, LookAt:=xlWhole, _
                                                MatchCase:=False, matchbyte:=False)
        If Not rFind Is Nothing Then Sheet1.Cells(rFind.Row, "A") = rFind.Value
    Next iWks
End Sub

**excelgrrl** · 12-14-2008, 07:07 PM

I receive a run time error '424' and it says "Object Required". The code highlighted is "Sheet1.Cells(rFind.Row, "A") = rFind.Value".

**shg** · 12-14-2008, 07:19 PM

Post a minimal workbook -- but first, please tell me you don't have merged cells.

Edit: Maybe you don't have a Sheet1. Change the line to

If Not rFind Is Nothing Then Worksheets(1).Cells(rFind.Row, "A") = rFind.Value

**excelgrrl** · 12-14-2008, 07:40 PM

I do not have any merged cells. I did change the line and it didn't seem to do anything. I didn't get the error though

Test1.xls

**shg** · 12-14-2008, 07:57 PM

I just ran it with the change I suggested and it worked fine.

**excelgrrl** · 12-14-2008, 07:33 PM

I didnt receive an error, but it didn't do anything either. I am confused.

**excelgrrl** · 12-14-2008, 08:03 PM

Okay - this time it did do something although I am not sure why it did what it did. This is not what I needed it to do. On sheet 1 (USERPROFILE) I want to look at each cell and search to see if it exists on any of the other sheets and if so I want to put User Name (or whatever text I decide) in column A in the same row the text I am searching for is in. For example- I search first for MODIFYUSER on all sheets and type Modified By in column A. Sheet 1 this would be row 29, Sheet 2 it doesn't exist, Sheet 3 this would be in row 7, etc.

**shg** · 12-14-2008, 08:07 PM

I need to find all sheets that contain the data "ContactID" and place (paste?) Contact ID in the same row, but in Column A.

That's what it does.

**excelgrrl** · 12-14-2008, 08:11 PM

Try doing the same with MODIFYUSER.

I just tried and it pasted the search text in random cells. I dont want to place the searched text in the cells.

If I find MODIFYUSER I want to place Modified By in the left adjusent cell. This is not what I am getting, maybe I am using your code incorrectly?

**excelgrrl** · 12-14-2008, 08:32 PM

okay after looking at this more, I found that it is finding the text and pasting it but, it is pasting it all on the same sheet, not the sheet that it was found on.

So if you could help me make that tweak as well as help me figure out how to make it paste something different then the text it is searching for.

Do you think this is possible.

**shg** · 12-14-2008, 08:45 PM

I'm happy to modify the code, EG, but you need to write a more lucid description of what you want. Taking your workbook as an example, you could manually do what you want done automatically, highlight the cells you modify, and explain why you did what you did.

Or just explain clearly what you want done.

**shg** · 12-14-2008, 09:05 PM

Rereading your post, maybe this:

Sub x()
    Dim sFind   As String
    Dim sVal    As String
    Dim iWks    As Long
    Dim rFind   As Range

    sFind = InputBox("Field?")
    If Len(sFind) = 0 Then Exit Sub
    sVal = InputBox("Value")
    If Len(sVal) = 0 Then Exit Sub
    
    For iWks = 1 To Worksheets.Count
        Set rFind = Worksheets(iWks).Cells.Find(What:=sFind, _
                                                LookIn:=xlValues, LookAt:=xlWhole, _
                                                MatchCase:=False, matchbyte:=False)
        If Not rFind Is Nothing Then Worksheets(iWks).Cells(rFind.Row, "A") = sVal
    Next iWks
    MsgBox "Done"
End Sub

... or maybe you want to take all the field names in the first sheet and copy the corresponding values in col A to the same column A adjacent to the same field name?

**excelgrrl** · 12-14-2008, 09:11 PM

Originally Posted by shg

Rereading your post, maybe this:

Sub x()
    Dim sFind   As String
    Dim sVal    As String
    Dim iWks    As Long
    Dim rFind   As Range

    sFind = InputBox("Field?")
    If Len(sFind) = 0 Then Exit Sub
    sVal = InputBox("Value")
    If Len(sVal) = 0 Then Exit Sub
    
    For iWks = 1 To Worksheets.Count
        Set rFind = Worksheets(iWks).Cells.Find(What:=sFind, _
                                                LookIn:=xlValues, LookAt:=xlWhole, _
                                                MatchCase:=False, matchbyte:=False)
        If Not rFind Is Nothing Then Worksheets(iWks).Cells(rFind.Row, "A") = sVal
    Next iWks
    MsgBox "Done"
End Sub

... or maybe you want to take all the field names in the first sheet and copy the corresponding values in col A to the same column A adjacent to the same field name?

This worked! Thank you so very much!! You are awesome!

**excelgrrl** · 12-14-2008, 09:06 PM

Okay, I did manually what I hope to acheive with a macro.

I have attached the new workbook. I have added comments to 4 of the cells on the first worksheet so you can see what I am trying to do. I have also colored coded the steps I took.

Test1.xls

**shg** · 12-14-2008, 09:18 PM

Got it -- maybe:

Sub x()
    Dim cell    As Range
    Dim rFind   As Range
    Dim iWks    As Long

    With Worksheets(1)
        For Each cell In .Range("B4", .Range("B4").End(xlDown))
            With cell
                If Not IsEmpty(.Offset(, -1).Value) Then
                    For iWks = 2 To Worksheets.Count
                        Set rFind = Worksheets(iWks).Columns(2).Find(What:=.Value, _
                                                                LookIn:=xlValues, LookAt:=xlWhole, _
                                                                MatchCase:=False, matchbyte:=False)
                        If Not rFind Is Nothing Then rFind.Offset(, -1).Value = .Offset(, -1).Value
                    Next iWks
                End If
            End With
        Next cell
    End With
    MsgBox "Done"
End Sub