Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Copy/Paste...Then Arrange Results in Columns
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 doesnt 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. Ill 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''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Copy/Paste...Then Arrange Results in Columns
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Copy/Paste...Then Arrange Results in Columns
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 A1:D9 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 theres 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Copy/Paste...Then Arrange Results in Columns
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Copy/Paste...Then Arrange Results in Columns
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex copy and paste looping VBA help | Excel Programming | |||
Complex Copy/Paste HELP.... | Excel Programming | |||
Complex Copy/Paste help | Excel Programming | |||
Complex Copy/Paste help | Excel Programming | |||
complex copy and paste | Excel Programming |