0

Let us say our first sheet's name is 'A'. How can we write data's sheet names in same column(B1) to sheet A. And again we search for SheetA's C1,D1 so on..

i found this code here this is useful but does not respond my need

Samar's code

Community
  • 1
  • 1
user2372931
  • 11
  • 2
  • When you say "we", you mean "How can I?" and the response to this is "What have YOU tried?" as per the [ask] page. – Skip Intro Jun 17 '13 at 11:52
  • We mean "How can I" sorry for conflict – user2372931 Jun 17 '13 at 11:58
  • 1
    can you be **clearer**? i can't understand what you are asking for... –  Jun 17 '13 at 12:06
  • As with mehow, I don't understand your question. If you need a function to put sheet A's name in sheet A, take a look at [this user-defined function](http://www.mrexcel.com/forum/excel-questions/78027-cell-value-%3D-sheet-name.html]). If you want to find out what sheet a string can be found on, you can get a start by recording a macro that uses used the Find & Replace command on the Home ribbon. – chuff Jun 17 '13 at 12:10
  • My excel file has 3 sheets their names are A,ABB,ABT.. In A only first row has string to B1 to H1 (A1 is empty). In ABB an ABT only column A has strings. First I want to search for string which in B1, if i find it in ABB i want to write ABB to B2. if i find it in ABT i want to write ABT to B3. All of these conditions are valid for C1,D1 and so on.. – user2372931 Jun 17 '13 at 12:28
  • Again, can you state what **you** have tried to accomplish this goal? – Skip Intro Jun 17 '13 at 13:26

1 Answers1

0

Lets say we are searching for "happiness" on each sheet except the first sheet. Give this a try:

Sub FindingHappiness()
Dim N As Long, M As Long
Dim s As String, r As Range
s = "happiness"
N = 10
For M = 2 To Sheets.Count
    Sheets(M).Activate
    For Each r In ActiveSheet.UsedRange
        If InStr(r.Value, s) > 1 Then
            Sheets(1).Cells(N, 2).Value = ActiveSheet.Name
            N = N + 1
            Exit For
        End If
    Next
Next
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99