Transpose in Reverse

[Brenner]

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

 

[Jim Cone]

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

 

[Brenner]

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

 

[Brenner]

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]

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