(Solved) – Pulling text from multiple worksheets


I’m looking to pull cells with certain text across multiple worksheets and put it into a new worksheet. I’m stuck on creating a loop, or just general code, that would let me use what I have across more than one worksheet.

Here’s my code:

 Sub EnzymeInteractions()
' Copy EPC cells Macro
 Dim bottomL As Integer
 Dim x As Integer
 bottomL = Sheets("Enzyme Interactions (110)").Range("I" & Rows.Count).End(xlUp).Row: x = 1

 Dim c As Range
 For Each c In Sheets("Enzyme Interactions (110)").Range("I:I" & bottomI)
 If c.Value = "EPC" Then
 c.EntireRow.Copy Worksheets("sheet4").Range("A" & x)
 x = x   1
 End If
 Next c

' CombineColumns Macro
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer

Set rng = ActiveCell.CurrentRegion
lastCell = rng.Columns(1).Rows.Count   1

For iCol = 2 To rng.Columns.Count
    Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
    ActiveSheet.Paste Destination:=Cells(lastCell, 1)
    lastCell = lastCell   rng.Columns(iCol).Rows.Count
Next iCol

' RemoveBlanks Macro
Selection.Delete Shift:=xlUp

End Sub

Everything works perfectly aside from the fact I don’t know how to use this marco across multiple worksheets (about 10). Hoping someone can please help!

Leave a Reply

Your email address will not be published. Required fields are marked *