Need to know how to copy into next blank column

[echane]

Hi, I'm trying to figure out how to copy data in a summary tab that is currently in a row format into other sheets with each row of data in its own column. The macro should know which tab to copy the information to based on the tab name.

I can get it to copy into the other tabs but it just keeps overwriting the same column

I've attached an example of what I want it to end up looking like.

Thanks in advance!

[Bob Phillips]

[vba]

Public Function ProcessData()
Dim sh As Worksheet
Dim This As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim NextRow As Long
Dim i As Long, j As Long

Set This = Worksheets("Summary")
This.Range("A3:C3").Value = Array("Tab name", "Fruit", "Tastiness Rating")
NextRow = 3
For Each sh In ActiveWorkbook.Worksheets

If Not sh Is This Then

LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
LastCol = sh.Cells(4, sh.Columns.Count).End(xlToLeft).Column
For i = 5 To LastRow

For j = 2 To LastCol

NextRow = NextRow + 1
sh.Range("A1").Copy This.Cells(NextRow, "A")
sh.Cells(4, j).Copy This.Cells(NextRow, "B")
sh.Cells(i, j).Copy This.Cells(NextRow, "C")
Next j
Next i
End If
Next sh

End Function
[/vba]