Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose in Reverse
Hi All - I have a nice little worksheet where cells change color from Red to Yellow to Green, as the users update their "percent complete" in their own local worksheets. Since there are many users and many processes per user, I have transposed the cell values and formats onto the next sheet, so that it shows management a graphical snapshot of where we are without all the clutter. Problem is - the graph is "transposed" upside down. How can I get it to transpose in reverse order to get my graph rightside up? Here is the code that I'm running now. Any help would be appreciated. Sheets("Process Flows").Select destcol = 3 For rowtocopy = 7 To 200 'Call the function for 200 rows so the processes can be added If IsInclusive(rowtocopy) Then destcol = destcol + 1 Range(Cells(rowtocopy, 15), Cells(rowtocopy, 30)).Select Selection.Copy Sheets("CRP Status").Select Cells(6, destcol).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Process Flows").Select End If Next rowtocopy Regards, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose in Reverse
John, This should work, as long as you are not using conditional formatting... '--- Sub TurnThemAround() Dim destCol As Long Dim LastCol As Long Dim FirstCol As Long Dim firstRow As Long Dim RowToCopy As Long firstRow = 6 FirstCol = 15 LastCol = 30 destCol = 3 For RowToCopy = 7 To 200 'Call the function for 200 rows so the processes can be added If IsInclusive(RowToCopy) Then 'Copy data destCol = destCol + 1 With Sheets("Process Flows") .Range(.Cells(RowToCopy, FirstCol), .Cells(RowToCopy, LastCol)).Copy End With 'Transpose on new sheet With Sheets("CRP Status").Cells(firstRow, destCol) .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=True .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=True End With End If Next 'RowToCopy Application.CutCopyMode = False 'Add values for AutoFill With Sheets("CRP Status") ..Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, _ destCol + 1)).Value = Application.Transpose(Array(1, 2)) 'AutoFill ..Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, _ destCol + 1)).AutoFill Destination:=.Range(.Cells(6, destCol + 1), _ .Cells(LastCol - FirstCol + firstRow, destCol + 1)), Type:=xlFillSeries 'Sort ..Range(.Cells(firstRow, 3), .Cells(LastCol - FirstCol + firstRow, _ destCol + 1)).Sort key1:=.Columns(destCol + 1), order1:=xlDescending 'Clear autofilled column ..Columns(destCol + 1).ClearContents End With End Sub -- Jim Cone Portland, Oregon USA "Brenner" wrote in message Hi All - I have a nice little worksheet where cells change color from Red to Yellow to Green, as the users update their "percent complete" in their own local worksheets. Since there are many users and many processes per user, I have transposed the cell values and formats onto the next sheet, so that it shows management a graphical snapshot of where we are without all the clutter. Problem is - the graph is "transposed" upside down. How can I get it to transpose in reverse order to get my graph rightside up? Here is the code that I'm running now. Any help would be appreciated. Sheets("Process Flows").Select destcol = 3 For rowtocopy = 7 To 200 'Call the function for 200 rows so the processes can be added If IsInclusive(rowtocopy) Then destcol = destcol + 1 Range(Cells(rowtocopy, 15), Cells(rowtocopy, 30)).Select Selection.Copy Sheets("CRP Status").Select Cells(6, destcol).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Process Flows").Select End If Next rowtocopy Regards, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose in Reverse
Hi Jim -
Thanks for the response! I'm not sure what to do with the bottom section of your code. Wherever I have ..Range - it all comes out in red. I haven't used ...Range before and am not sure how to deal with it. Just a hint is proly all I need. Again, thanks for taking the time... John (This part...) 'Add values for AutoFill With Sheets("CRP Status") ...Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, destCol + 1)).Value = Application.Transpose(Array(1, 2)) 'AutoFill ...Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, _ destCol + 1)).AutoFill Destination:=.Range(.Cells(6, destCol + 1), _ .Cells(LastCol - FirstCol + firstRow, destCol + 1)), Type:=xlFillSeries 'Sort ...Range(.Cells(firstRow, 3), .Cells(LastCol - FirstCol + firstRow, _ destCol + 1)).Sort key1:=.Columns(destCol + 1), order1:=xlDescending 'Clear autofilled column ...Columns(destCol + 1).ClearContents -- Cool "Jim Cone" wrote: John, This should work, as long as you are not using conditional formatting... '--- Sub TurnThemAround() Dim destCol As Long Dim LastCol As Long Dim FirstCol As Long Dim firstRow As Long Dim RowToCopy As Long firstRow = 6 FirstCol = 15 LastCol = 30 destCol = 3 For RowToCopy = 7 To 200 'Call the function for 200 rows so the processes can be added If IsInclusive(RowToCopy) Then 'Copy data destCol = destCol + 1 With Sheets("Process Flows") .Range(.Cells(RowToCopy, FirstCol), .Cells(RowToCopy, LastCol)).Copy End With 'Transpose on new sheet With Sheets("CRP Status").Cells(firstRow, destCol) .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=True .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=True End With End If Next 'RowToCopy Application.CutCopyMode = False 'Add values for AutoFill With Sheets("CRP Status") ..Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, _ destCol + 1)).Value = Application.Transpose(Array(1, 2)) 'AutoFill ..Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, _ destCol + 1)).AutoFill Destination:=.Range(.Cells(6, destCol + 1), _ .Cells(LastCol - FirstCol + firstRow, destCol + 1)), Type:=xlFillSeries 'Sort ..Range(.Cells(firstRow, 3), .Cells(LastCol - FirstCol + firstRow, _ destCol + 1)).Sort key1:=.Columns(destCol + 1), order1:=xlDescending 'Clear autofilled column ..Columns(destCol + 1).ClearContents End With End Sub -- Jim Cone Portland, Oregon USA "Brenner" wrote in message Hi All - I have a nice little worksheet where cells change color from Red to Yellow to Green, as the users update their "percent complete" in their own local worksheets. Since there are many users and many processes per user, I have transposed the cell values and formats onto the next sheet, so that it shows management a graphical snapshot of where we are without all the clutter. Problem is - the graph is "transposed" upside down. How can I get it to transpose in reverse order to get my graph rightside up? Here is the code that I'm running now. Any help would be appreciated. Sheets("Process Flows").Select destcol = 3 For rowtocopy = 7 To 200 'Call the function for 200 rows so the processes can be added If IsInclusive(rowtocopy) Then destcol = destcol + 1 Range(Cells(rowtocopy, 15), Cells(rowtocopy, 30)).Select Selection.Copy Sheets("CRP Status").Select Cells(6, destcol).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Process Flows").Select End If Next rowtocopy Regards, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose in Reverse
Hi again, Jim -
I just took out one of the periods before Range - and it works like a champ! Problem solved. Very nice code... John "Jim Cone" wrote: John, This should work, as long as you are not using conditional formatting... '--- Sub TurnThemAround() Dim destCol As Long Dim LastCol As Long Dim FirstCol As Long Dim firstRow As Long Dim RowToCopy As Long firstRow = 6 FirstCol = 15 LastCol = 30 destCol = 3 For RowToCopy = 7 To 200 'Call the function for 200 rows so the processes can be added If IsInclusive(RowToCopy) Then 'Copy data destCol = destCol + 1 With Sheets("Process Flows") .Range(.Cells(RowToCopy, FirstCol), .Cells(RowToCopy, LastCol)).Copy End With 'Transpose on new sheet With Sheets("CRP Status").Cells(firstRow, destCol) .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=True .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=True End With End If Next 'RowToCopy Application.CutCopyMode = False 'Add values for AutoFill With Sheets("CRP Status") ..Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, _ destCol + 1)).Value = Application.Transpose(Array(1, 2)) 'AutoFill ..Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, _ destCol + 1)).AutoFill Destination:=.Range(.Cells(6, destCol + 1), _ .Cells(LastCol - FirstCol + firstRow, destCol + 1)), Type:=xlFillSeries 'Sort ..Range(.Cells(firstRow, 3), .Cells(LastCol - FirstCol + firstRow, _ destCol + 1)).Sort key1:=.Columns(destCol + 1), order1:=xlDescending 'Clear autofilled column ..Columns(destCol + 1).ClearContents End With End Sub -- Jim Cone Portland, Oregon USA "Brenner" wrote in message Hi All - I have a nice little worksheet where cells change color from Red to Yellow to Green, as the users update their "percent complete" in their own local worksheets. Since there are many users and many processes per user, I have transposed the cell values and formats onto the next sheet, so that it shows management a graphical snapshot of where we are without all the clutter. Problem is - the graph is "transposed" upside down. How can I get it to transpose in reverse order to get my graph rightside up? Here is the code that I'm running now. Any help would be appreciated. Sheets("Process Flows").Select destcol = 3 For rowtocopy = 7 To 200 'Call the function for 200 rows so the processes can be added If IsInclusive(rowtocopy) Then destcol = destcol + 1 Range(Cells(rowtocopy, 15), Cells(rowtocopy, 30)).Select Selection.Copy Sheets("CRP Status").Select Cells(6, destcol).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Sheets("Process Flows").Select End If Next rowtocopy Regards, John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose in Reverse
John,
There should only be one dot in front of "range". The dot ties the range to the sheet object in the "With" statement. 'For instance... With Sheets(2) .Range("A1").Value = 10 End With 'Is the same as... Sheets(2).Range("A1").Value = 10 Again, please note that the Conditional Formatting in cells does not sort. -- Jim Cone Portland, Oregon USA "Brenner" wrote in message Hi Jim - Thanks for the response! I'm not sure what to do with the bottom section of your code. Wherever I have ..Range - it all comes out in red. I haven't used ...Range before and am not sure how to deal with it. Just a hint is proly all I need. Again, thanks for taking the time... John (This part...) 'Add values for AutoFill With Sheets("CRP Status") ...Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, destCol + 1)).Value = Application.Transpose(Array(1, 2)) 'AutoFill ...Range(.Cells(firstRow, destCol + 1), .Cells(firstRow + 1, _ destCol + 1)).AutoFill Destination:=.Range(.Cells(6, destCol + 1), _ .Cells(LastCol - FirstCol + firstRow, destCol + 1)), Type:=xlFillSeries 'Sort ...Range(.Cells(firstRow, 3), .Cells(LastCol - FirstCol + firstRow, _ destCol + 1)).Sort key1:=.Columns(destCol + 1), order1:=xlDescending 'Clear autofilled column ...Columns(destCol + 1).ClearContents -- Cool |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reverse Transpose? | Excel Worksheet Functions | |||
how paste transpose reverse? | Excel Discussion (Misc queries) | |||
How do I transpose the figures to go in reverse order in a bar gr. | Charts and Charting in Excel | |||
reverse value | Excel Worksheet Functions | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions |