Automatically updating one sheet, from another

[Guest]

Here's my situation, I have largely horizontal spread sheet, with vertical
info as well, we are trying to consolidate all info into 1 vertical column,
is there a way to copy each cell from sheet 2 in a range of rows i.e (8:100)
and then copy that into colum A of sheet 1?

Sheet 2 example
a b c d e f g h
i j k l m n o
p q r s t
u v w x
y
z

then need this transfered into sheet1 as
a
b
c
d
e
f
g
....

Please help.

 

[Guest]

Try

Copy > PasteSpecial > Transpose

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

You need to run the macro below

Sub Movedata()

Sh1RowCount = 1
Sh2RowCount = 1
With Sheets("sheet2")
Do While Not IsEmpty(.Cells(Sh2RowCount, Columns.Count). _
End(xlToLeft))

LastColumn = .Cells(Sh2RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastColumn

If Not IsEmpty(.Cells(Sh2RowCount, ColCount)) Then
.Cells(Sh2RowCount, ColCount).Copy _
Destination:=Sheets("Sheet1").Cells(Sh1RowCount, "A")
Sh1RowCount = Sh1RowCount + 1
End If
Next ColCount
Sh2RowCount = Sh2RowCount + 1
Loop
End With
End Sub

 

[Guest]

Thank you! Now another question is there anyway to modify this so that it
loads only new data from sheet 2 to the bottom of Column A on sheet 1 after
i've run the Move Data? For example, i use MoveData to populate Column A with
all my data from sheet 2, now i add info to sheet2, is there a similar macro
to run that would move only new data added?

Thanks

 

[Guest]

I made a few changes

Sub Movedata()

With Sheets("Sheet1")
Lastrow = .Cells(Rows.Count, "A"). _
End(xlUp).Row
If (Lastrow = 1) And IsEmpty(.Cells(Rows.Count, "A")) Then
Sh1RowCount = 1
Else
Sh1RowCount = Lastrow + 1
End If
End With
Sh2RowCount = 1
With Sheets("sheet2")
Do While Not IsEmpty(.Cells(Sh2RowCount, Columns.Count). _
End(xlToLeft))

LastColumn = .Cells(Sh2RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastColumn

If Not IsEmpty(.Cells(Sh2RowCount, ColCount)) Then
Mydata = .Cells(Sh2RowCount, ColCount)
With Sheets("Sheet1")
Set c = .Columns("A:A").Find(what:=Mydata, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then

.Cells(Sh1RowCount, "A") = Mydata
Sh1RowCount = Sh1RowCount + 1
End If
End With
End If
Next ColCount
Sh2RowCount = Sh2RowCount + 1
Loop
End With
End Sub