0

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

halfer
  • 19,824
  • 17
  • 99
  • 186
shellyisrani
  • 37
  • 5

1 Answers1

1

You can add a parameter to your sub and pass in each worksheet to be processed as an argument.

sub Main()
    EnzymeInteractions Sheets("Enzyme Interactions (110)")
    EnzymeInteractions Sheets("Enzyme Interactions (120)")
    'etc
End sub


Sub EnzymeInteractions(ws As Worksheet)

    'use ws instead of (eg) Sheets("Enzyme Interactions (110)")

End Sub

You do need to fix the second half of your sub to remove the use of ActiveCell/ActiveSheet: you should always use explicit range/sheet references where you can.

See: How to avoid using Select in Excel VBA for guidelines on that.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Ok so I understand your code, and initially it seems to work; I run the macro and I see the data written in my select worksheet, however as it runs the same column gets overridden with different data, so that in the end only one worksheet's data is visible. Any idea of how to fix it? – shellyisrani Apr 14 '20 at 23:06
  • You will need to modify your code so the `EnzymeInteractions` sub can determine where the last used row is on the destination sheet (lots of posts here on that topic) and start copying at the next row. – Tim Williams Apr 15 '20 at 03:35