PDA

View Full Version : Need to know how to copy into next blank column



echane
11-11-2009, 11:23 PM
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
11-12-2009, 02:04 AM
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