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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

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?


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"joel" wrote:

>
> The way you posted the data I'm a little confused where the data is
> 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
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197490
>
> http://www.thecodecage.com/forumz
>
> .
>

Awesome! Thanks so much, Joel!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"joel" wrote:

>
> Your orignal posting didn't show columns E to G. You want to match the
> 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)
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197490
>
> http://www.thecodecage.com/forumz
>
> .
>