I have an excel workbook with 500 worksheets. An example worksheet is shown in the image below. I am only interested in the data in column E of every one of these 500 sheets.

Is there an easy way for me to take column E from every worksheet and put them in a new worksheet?

I don't want to do any calculations on them, just display all 500 column Es in a new worksheet.

I've been googling and can't find a solution.

Format of the worksheets:

Format of worksheets

  • In each column of the new worksheet, point to E1 of a different worksheet. Then copy the row down. If there's logic to the sheet names, you might be able to do it in one formula. – fixer1234 Feb 26 '18 at 21:31

This could be accomplished relatively easily using VBA.

  • Open the Visual Basic IDE using Alt+F11
  • Insert a new public module using Alt+I, M
  • Paste the following code into the module:

    Sub GrabColumnE()
        Dim shtCoE As Worksheet
        Dim shtSrc As Worksheet
        Dim lngCol As Long: lngCol = 1
        Set shtCoE = Sheets.Add(After:=Sheets(Sheets.Count))
        For Each shtSrc In Sheets
            If Not shtSrc Is shtCoE Then
                shtSrc.Columns(5).Copy shtCoE.Columns(lngCol)
                lngCol = 1 + lngCol
            End If
        Next shtSrc
    End Sub
  • Either press F5 with the cursor within the code to run the Sub, or go to the Developer tab on the ribbon, click on Macros, select the macro with the above name and click Run.

I hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.