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--

 

[joel]

The way you posted the data I'm a little confused where the data i
located. See if you can modify this code to do what you want to do.

Sub Movedata()

Set SourceSht = Sheets("sheet1")
Set DestSht = Sheets("sheet2")

FirstRow = 1 'the row where A is located
SecondRow = 4 'the row where AA is located
RowOffset = SecondRow - FirstRow
NewRowCount = 1 'row where data starts in destination sheet

With SourceSht
For RowCount = FirstRow To (SecondRow - 1)
FirstRowHeader = .Range("A" & RowCount)
SecondRowHeader = .Range("A" & (RowCount + RowOffset))
For ColCount = 2 To 4
FirstData = .Cells(RowCount, ColCount)
SecondData = .Cells(RowCount + RowOffset, ColCount)
With DestSht
.Range("A" & NewRowCount) = FirstRowHeader
.Range("B" & NewRowCount) = FirstData
.Range("C" & NewRowCount) = SecondRowHeader
.Range("D" & NewRowCount) = SecondData
NewRowCount = NewRowCount + 1
End With
Next ColCount
Next RowCount
End With

End Sub

 

[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?

 

[joel]

Your orignal posting didn't show columns E to G. You want to match th
following columns


B (2) to E (5)
C (3) to F (6)
D (4) to G (7)


You need to add 3 columns to the column number

From:
For ColCount = 2 To 4
FirstData = .Cells(RowCount, ColCount)
SecondData = .Cells(RowCount + RowOffset, ColCount)

To:
For ColCount = 2 To 4
FirstData = .Cells(RowCount, ColCount + 3) 'add 3 to get columns E to G
SecondData = .Cells(RowCount + RowOffset, ColCount)

 

[ryguy7272]

Awesome! Thanks so much, Joel!!