Complex Copy/Paste...Then Arrange Results in Columns

[ryguy7272]

Hello experts! I have a question about copying/pasting data from one sheet
to another. I was playing with some code, which I can share, but it doesn’t
do what I want, so it may just be more confusing than helpful.

Basically, I want to take all data in row 2 on my ‘Sheet1’ and copy/paste to
‘SummarySheet’, with the correct value in ColumnA. I’ll just show you the
layout…
So, my data looks linke this:
A B C AA BB CC
A 5 1 9
B 4 6 2
C 3 7 8
AA -5 -4 -3
BB -1 -6 -7
CC -9 -2 -8


I want it to look like this:
A 5 AA -5
A 1 AA -4
A 9 AA -3
B 4 BB -1
B 6 BB -6
B 2 BB -7
C 3 CC -9
C 7 CC -2
C 8 CC -8

Notice, there are several blank cells in the range. I want to check the
used range; there will be many more rows and many more columns with the
actual data.

Thanks!
Ryan--

 

[ryguy7272]

That's almost it Joel, but not quite. I wish I was better at these
copy/paste-b/w-sheet-things!! Not sure why the data shows like that in the
view; when I pasted it into the window to upload to the discussion group, it
displayed fine!! Anyway, here's my data layout:

Columns E:G
Row1 AA BB CC
Row2 5 1 9
Row3 4 6 2
Row4 3 7 8

Columns A:C
Row 5 AA -5 -4 -3
Row6 BB -1 -6 -7
Row7 CC -9 -2 -8

In A19 I want to see this:
A 5 BB -5
A 1 AA -4
A 9 CC -3
B 4 CC -1
B 6 AA -6
B 2 CC -7
C 3 AA -9
C 7 BB -2
C 8 CC -8

Notice, there are some blank cells! I was thinking of something like this:
For Each Cell In Range("a1:j10" & ActiveSheet.UsedRange.Rows.Count)
If Cell.Value <> "" Then

' code...

End If
Next

.. . . But there’s a little more to it than just that! Ultimately, there
will be MANY rows and many columns. So I think I need to test for something
like this: If Cell.Value <> "" Then

Also, I think I need something like this:
'Assume start position is 1,1
lngLastRow = wsSheet1.Cells(Rows.Count, "A").End(xlUp).Row - 1
lngLastCol = wsSheet1.Cells(1, Columns.Count).End(xlToLeft).Column

Somehow, I think I need to test for cells with values, or the final result
will have lots of blanks, right. The data come from a (crazy) query. The
upper right hand quadrant has data and the lower left hand quadrant has data
(the mirror image of the upper right hand quadrant); the upper left hand
quadrant contains blanks and the lower right hand quadrant contains blanks.

Make sense?

 

[ryguy7272]

Awesome! Thanks so much, Joel!!