Extracting data from multiple tabs into next available column in output sheet using VBA

Question

I have a lot of different workbooks that have multiple tabs that I need to extract data from to summarise. Unfortunately they are all in columns so each data point is in its own column with the categories in rows down to 50.

I need to be able to copy all of the used columns bar column A in each sheet into an output sheet named "Samples". So for each new tab I need the data to be pasted to the next available column in the output sheet.

The following is the code that I have written but on the second loop I get an object defined error.

Can anyone point me in the right direction? I'm pretty new to all this!

Sub ExtractSamples()
    Set wsOutput = ActiveWorkbook.Sheets("Samples")


For Each wsInput In ActiveWorkbook.Worksheets

    If wsInput.Name <> wsOutput.Name Then

        With wsInput

            LColI = .Cells(1, .Columns.Count).End(xlToLeft).Column

            Set rng = .Range(.Cells(1, 2) & .Cells(50, LColI))

            rng.Copy

            With wsOutput

                LColO = .Range("A" & .Columns.Count).End(xlToLeft).Column + 1


                .Range("A" & LColO).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End With
        End With
    End If
Next wsInput

End Sub

Thanks so much!

Correct this line Set rng = .Range(.Cells(1, 2), .Cells(50, LColI)) and tell us which line errors. You should use a similar formulation for LColO. — SJR
Where exactly does the error occur? What value is held in LColO and LColI when it happens? — CLR

SJR SJR · Accepted Answer · 2017-04-05T10:27:30

Try this. I've corrected the syntax as above (your assignation of LColO didn't make sense because A is a column and range wants a row and a column). I have also declared all your variables, which is good practice even if it probably didn't cause an error in this instance.

Sub ExtractSamples()

Dim wsOutput As Worksheet, wsInput As Worksheet
Dim LColI As Long, LColO As Long

Set wsOutput = ActiveWorkbook.Sheets("Samples")

For Each wsInput In ActiveWorkbook.Worksheets
    If wsInput.Name <> wsOutput.Name Then
        With wsInput
            LColI = .Cells(1, .Columns.Count).End(xlToLeft).Column
            Set Rng = .Range(.Cells(1, 2), .Cells(50, LColI))
            Rng.Copy
            With wsOutput
                LColO = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
                .Cells(1, LColO).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End With
        End With
    End If
Next wsInput

End Sub

Extracting data from multiple tabs into next available column in output sheet using VBA

1 Answers