On Worksheet1 I've got 6 sets of ranges (D34:D36, D50:D62, D66:D78, and so on
for a total of 6 ranges) that may contain the string "ACS" somewhere in the
cell.

I want to search these ranges, and copy both cells to the left of the cell
containing "ACS", the actual cell containing "ACS", and the cell directly to
the right to Worksheet2.

For example,
Worksheet1!D50 contains "ACS"
so
the cells in Worksheet1!B50:E50 are copied to a range in Worksheet2 (A8:D20)
specifically in the first row of the range A8:D8, because it is the first
instance of "ACS" found in the source cells.

When "ACS" is found again in Worksheet1 (say in cell D60),
the cells in Worksheet1!B60:E60 are copied to the 2nd row of the range in
Worksheet2 (A9:D9).

And so on until all the ranges in Worksheet1 have been searched.