VBA Code needed to copy data across Multiple Tabs from same column into one column

[Marsman]

Good afternoon - could I get some help on being able to move all the data in Column "I" (eye) across all tabs (with the exception of the tab named "Summary") into one column of the "summary" tab?

Currently I have a script running which collects all the data in each tab and it copies it to the Summary tab where the data from each tab is in its own column then I am manually moving all the column data into one column.

{Script}

Sub Transponder_Gathering()
Dim C As Integer
Dim WS As Worksheet
C = 1
For Each WS In Worksheets
If WS.Name <> "Summary" Then
WS.Range("I:I").Copy Destination:=Sheets("Summary").Cells(, C).EntireColumn
C = C + 1
Else
End If
Next WS
End Sub

 

[Weaver]

Something like:

Sub Transponder_Gathering()
  Dim smry As Worksheet, ws As Worksheet
    Set smry = Sheets("summary")
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            ws.Range("I1", ws.Cells(Rows.Count, "I").End(xlUp)).Copy _
                Destination:=smry.Cells(Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next ws
End Sub

 

[Marsman]

Good afternoon - could I get some help on being able to move all the data in Column "I" (eye) across all tabs (with the exception of the tab named "Summary") into one column of the "summary" tab?

Currently I have a script running which collects all the data in each tab and it copies it to the Summary tab where the data from each tab is in its own column then I am manually moving all the column data into one column.

{Script}

Sub Transponder_Gathering()
Dim C As Integer
Dim WS As Worksheet
C = 1
For Each WS In Worksheets
If WS.Name <> "Summary" Then
WS.Range("I:I").Copy Destination:=Sheets("Summary").Cells(, C).EntireColumn
C = C + 1
Else
End If
Next WS
End Sub

Works exactly as i need. Is there a easy enough code to delete any of the blank rows all in this same process? Not each sheet is exactly the sam number of data points and the data in not sequentially filled in.


I1,I2,I3,I4,I8

So on the Summary page I would need those blank rows, that would be represent I5, I6 and i7 (examples), be deleted from the Summary page.

 

[Oeldere]

You can use this code to delete the empty rows in column A.

.Range("A2:A" & .Rows.Count).SpecialCells(4).EntireRow.Delete

 

[Marsman]

You can use this code to delete the empty rows in column A.

.Range("A2:A" & .Rows.Count).SpecialCells(4).EntireRow.Delete

This code works great.

ray:

Thanks

 

[Oeldere]

Thanks for the reply.

Glad I could help.