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 Cells.Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Shift:=xlUp Range("A9").Select 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!