Find Data & Paste in pervious column on multiple sheets

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?

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

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

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.

Sorry. No it doesn't.

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

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

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.

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?

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.

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

Originally Posted by shg

Rereading your post, maybe this:

Please Login or Register  to view this content.

... 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!

Thanks for the help - the solution worked!