very complicated transpose

Okay here it goes, The attached book on the day sheet needs to have columns c-p from row 1-37 need to be transposed to another book. That is the simple part. The problem I am having is that there needs to be multiple copies of each column based on the number of specific cells in that column that are greater than 0 (60,62,64,66,68,70,72,74,76,78,80,82). So if column c has rows 60,64,70 greater than 0,then there would be three copies of column c transposed. Is this a possibility at all? and does anyone want to take on the challenge?

Hello there,

I don't think this would be too hard to do but I'm a little confused by your example. You are stating column C having a value greater than 0, which rows are to be included when applying this rule? And to where are you copying and pasting the information?

Thanks!

RVASQUEZ

C62,c64,c66,c68,c70,c72,c74,c76,c78,c80,c82 any of those

Can you update you workbook to mimic what you would like the end result to look like?

Thanks!

okay i have made a mock up and on this version sheet three will be the depository. There are 4 symbols here marked"A""B""C""D" those are the columns and in the area of rows 59 to 82 there are 4 brokers"UBS" "AEGIS" "CANN" "MCLA" The end goal for this section of 59 to 82 is that if any of the even numbered rows are greater than 0 then that column will be copied from rows 1-37 and transposed values only. If possible I would like the corresponding broker could be included that can be found in a59-a82.

I know If I was better at this I could explain this more clearly.

Thanks for your help

This is absolutely amazing. Thank you very much. I actually only see one issue, Column M on sheet three equals the same as column L. Would it be possible to have Column M equal the Cell above what column L equals on the daily sheet. So for UBS on Symbol A on the day sheet, column L would equal C60. Ideally Column M would equal C59.

Thanks Again,
Gherzberg

Hello there,

Update your code to look like the following, I've highlighted the lines that have added/changed:

Sub TransferToSheet3()
Dim i As Long, x As Long, Sht3 As Worksheet, u As Long, LR As String    'declare variables

Set Sht3 = Sheets("Sheet3") 'set the variable Sht3 equal to the worksheet named Sheet3
    
With Sheets("DaY")  'with the worksheet DaY
    For x = 3 To 6  'start x at three and loop through until 6
                    'x will be used to reference the columns C, D and E in the worksheet DaY
        For i = 60 To 82 Step 2 'loop through the variable i starting at 60 and going to 82
                                'looping through only the even numbers
                                'i will be used to reference the IOI/ALLO's cells and whether they contain a value
                                'so you can determine whether to transfer information or not
            If .Cells(i, x) > 0 Then    'if the value in the cell in row i(the current number in the i loop)
                                        'column x (the current x in the x loop) is greater than 0 then...
                LR = Sht3.Range("A6555").End(xlUp).Row + 1  'set LR equal to the first empty row in the worksheet Sheet3
                For u = 1 To 37 'loop through the variable u from 1 to 37
                    Select Case u   'if the current u in the loop is...
                        Case 3  'the number 3 (column C (BROKER)) then..
                            Sht3.Cells(LR, u) = .Cells(i - 1, 1)    'set the cell (worksheet sheet3) in row LR(defined above), column u (the current u in the u loop)
                                                                    'equal to the cell (worksheet DaY) in row i-1 (current i in the loop minus 1), column A
                        Case 12 'the number is 12 (column L) then...
                            Sht3.Cells(LR, u) = .Cells(i, x)    'set the cell (worksheet sheet3) in row LR(defined above), column u (the current u in the u loop)
                                                                'equal to the cell (worksheet DaY) in row i (current i in the loop ), column x (current x in the loop)
                        Case 13 'the number is 13 (column M) then...
                            Sht3.Cells(LR, u) = .Cells(i - 1, x)  'set the cell (worksheet sheet3) in row LR(defined above), column u (the current u in the u loop)
                                                                'equal to the cell (worksheet DaY) in row i (current i in the loop ), column x (current x in the loop)                        
                        Case Else
                            Sht3.Cells(LR, u) = .Cells(u, x)    'set the cell (worksheet sheet3) in row LR(defined above), column u (the current u in the u loop)
                                                                'equal to the cell (worksheet DaY) in row u (current u in the loop ), column x (current x in the loop)
                    End Select
                Next u  'move to next u in the u loop
            End If
        Next i  'move to next i in the i loop
    Next x  'move to next x in the x loop
End With
    
End Sub

* If this answers your thread, please mark this thread solved and maybe give a little star tap to add to my reputation points if I helped.
Thanks!

Thanks that works perfectly. If you want another challenge I need that same code but it only copies if the broker(Day sheet A59 to A82) is "MSKR CASH" "WFABK CASH" or "MLCA CASH". If you could help i would surely appreciate it.

Hello there,

For this update your code to the following:
I've changed the font color to red for all information that was added.

Sub TransferToSheet3()
Dim i As Long, x As Long, Sht3 As Worksheet, u As Long, LR As String    'declare variables

Set Sht3 = Sheets("Sheet3") 'set the variable Sht3 equal to the worksheet named Sheet3
    
With Sheets("DaY")  'with the worksheet DaY
    For x = 3 To 6  'start x at three and loop through until 6
                    'x will be used to reference the columns C, D and E in the worksheet DaY
        For i = 60 To 82 Step 2 'loop through the variable i starting at 60 and going to 82
                                'looping through only the even numbers
                                'i will be used to reference the IOI/ALLO's cells and whether they contain a value
                                'so you can determine whether to transfer information or not
            If .Cells(i, x) > 0 Then    'if the value in the cell in row i(the current number in the i loop)
                                        'column x (the current x in the x loop) is greater than 0 then...
                                        
                Select Case .Cells(i - 1, 1).Value
                    Case "MSKR CASH", "WFABK CASH", "MLCA CASH"                        
                        LR = Sht3.Range("A6555").End(xlUp).Row + 1  'set LR equal to the  first empty row in the worksheet Sheet3
                        For u = 1 To 37 'loop through the variable u from 1 to 37
                            Select Case u   'if the current u in the loop is...
                                Case 3  'the number 3 (column C (BROKER)) then..
                                    Sht3.Cells(LR, u) = .Cells(i - 1, 1)    'set the cell (worksheet sheet3) in row LR(defined above), column u (the current u in the u loop)
                                                                            'equal to the cell (worksheet DaY) in row i-1 (current i in the loop minus 1), column A
                               Case 12 'the number is 12 (column L) then...
                                    Sht3.Cells(LR, u) = .Cells(i, x)    'set the cell (worksheet sheet3) in row LR(defined above), column u (the current u in the u loop)
                                                                        'equal to the cell (worksheet DaY) in row i (current i in the loop ), column x (current x in the loop)
                                Case 13 'the number is 13 (column M) then...
                                    Sht3.Cells(LR, u) = .Cells(i - 1, x)  'set the cell (worksheet sheet3) in row LR(defined above), column u (the current u in the u loop)
                                                                        'equal to the cell (worksheet DaY) in row i (current i in the loop ), column x (current x in the loop)
                                Case Else
                                    Sht3.Cells(LR, u) = .Cells(u, x)    'set the cell (worksheet sheet3) in row LR(defined above), column u (the current u in the u loop)
                                                                        'equal to the cell (worksheet DaY) in row u (current u in the loop ), column x (current x in the loop)
                                End Select
                        Next u  'move to next u in the u loop
              End Select            
            End If
        Next i  'move to next i in the i loop
    Next x  'move to next x in the x loop
End With
    
End Sub

Rvasquez,
I wonder if you could help me with the code. On the original we had it copying any column that had even numbered cells from 60 to 82. I had to add a row above and now the vba script is not copying correctly. Now I need the cells to be odd numbers from 61 to 83.