title: Copy/Paste column range based on cell value
body: <p>I have an extensive workbook with around 500 custom named worksheets that have been created by a macro that has manipulated data from a core dataset. All worksheets follow an identical format.</p>
  <p>Within the range QG27:SO27 on every one of these ~500 worksheets there is a formula that shows &quot;TRUE&quot; if all the above cells meet a certain criteria, otherwise they are blank</p>
  <p>My challenge is to collate the &quot;TRUE&quot; data to a separate sheet named &quot;COLLATED TRUE VALUES&quot;. By scanning through QG27:SO27 on each worksheet, if a cell in QG27:SO27 contains &quot;TRUE&quot; then copy that column from row 1:27 and paste to C2 of sheet named &quot;COLLATED TRUE VALUES&quot; and copy/paste the sheet name it was extracted from into C1. Each additional &quot;TRUE&quot; encountered will copy/paste the same corresponding data to the next column in the &quot;COLLATED TRUE VALUES&quot; sheet and continue through all worksheets</p>
  <p>I have considered a loop through the range that may contain &quot;TRUE&quot; and step through each of the 500 sheets but his would be a slow process and I expect to need to reuse this type of scenario with many other workbooks.</p>
  <p>I would like some help creating a macro that can collate the required date in the most efficient way</p>