Find, Copy, Paste, VB code

[Tortus]

Thanks in advanced for making this forum possible. I don’t know what some of us would do without information available like this. I know a little, but I’m not very efficient with xls.

I’m working in excel in an attempt to move data from several sheets within a book to one single sheet in the same book. The data I want to move is not always in the same cell. The problem is that I need help cleaning this data so that I get exactly what I would like to see in the single sheet. Looking at this code you can see that I am moving from a bunch of sheets,“A26:E30” to a single sheet (this is working). The cell A26 is not always the same. What I would like to move is, Example: “TEXT:TEXT”. So now the code is looking for “words” instead of columns and rows. The code I have so far that is creating a new sheet within a workbook and coping and pasting "a26:e30" from each sheet within the book is as follows.

Sub text_Alarms1Sheet()

' Compiled by “ME” 5/4/2007 "Took me all day..."
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete sheet "Standing Alarms" if exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Standing Alarms").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add "Standing Alarms" sheet
    Set DestSh = ThisWorkbook.Worksheets.add
    DestSh.Name = "Standing Alarms"

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In Sheets(Array("1D", "1N", "2D", "2N", "3d", "3n", "4d", "4n", "5d", "5n", "6d", "6n", "7d", "7n", "8d", "8n", "9d", "9n", "10d", "10n", "11d", "11n", "12d", "12n", "13d", "13n", "14d", "14n", "15d", "15n", "16d", "16n", "17d", "17n", "18d", "18n", "19d", "19n", "20d", "20n", "21d", "21n", "22d", "22n", "23d", "23n", "24d", "24n", "25d", "25n", "26d", "26n", "27d", "27n", "28d", "28n", "29d", "29n", "30d", "30n", "31D", "31N"))
        'If sh.Name <> DestSh.Name Then
            Last = LastRow(DestSh)

            'This example copies everything
            sh.Range("A26:E30").Copy DestSh.Cells(Last + 1, "a")
            'This will copy the sheet name in the H column
            DestSh.Cells(Last + 1, "H").Value = sh.Name

        'End If
    Next

    Application.GoTo DestSh.Cells(1)

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

[/code]

 

[Norie]

Do you mean you are searching for "TEXT:TEXT"?

Or do you want to construct a string for the range to copy?

Note the For can be done like this.

For each sh In Worksheets

 

[Tortus]

Well, the cell A26 has "Standing Alarms" in every sheet. What I would need the code to do is pick out items just below that cell which consist of different names. So... there is not many cells below the cell that contains "Standing Alarm", but at some point other data is on this sheet and I don't want that copied to the new sheet.
Example: First sheet
STANDING ALARMS ITEMS COMMENTS
YA1 tags2,3 yata yata
UA3 tags18m yata yata

Next Sheet
STANDING ALARMS ITEMS COMMENTS
YQ1 tags6,7 yata yata
UT3 tags18m yata yata

 

[Norie]

Sorry I'm a little confused.

Do you want to copy all the data from A26 down to the last row of data across to column E?

And the data isn't contiguous?

 

[Tortus]

Ok, I guess it’s harder to explain than I thought. I’ll try to give more detail.

1 workbook with many sheets. Within each sheet there is a section that lists “Standing Alarms” I want to copy columns and rows that have information about standing alarms.
I can not ask for exact cells because “Standing Alarms” is not always in cell “A26” but it is always in “A” column. Any ideas?