very complicated transpose

[gherzberg]

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?

[rvasquez]

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

[gherzberg]

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

[rvasquez]

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

Thanks!

[gherzberg]

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.

[gherzberg]

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

Thanks for your help

[rvasquez]

Attached is you mock workbook updated to include the macro TransferToSheet3 which I believe accomplishes what you are trying to achieve. Below is the code.

Please Login or Register  to view this content.

Open the workbook and press Alt+F8 to bring up the macro window. Then select the TransferToSheet3 macro and select Run. Go to the worksheet Sheet3 and the information should appear in row 21 under the Testing Below header.

To insert this code into your workbook

1. Press Alt+F8
2. Clear the macro name field and then type TransferToSheet3
3. Select the create option
4. Copy and paste the above code in between the Sub TransferToSheet3() and End Sub
5. Take a moment to read through the code, I have left comments that will appear in green that will let you know what each line of the code is doing.
6. Close out of Visual Basic

Let me know if this works for you!

Thanks!

[gherzberg]

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

[rvasquez]

Hello there,

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

Please Login or Register  to view this content.

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

[gherzberg]

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.

[rvasquez]

Hello there,

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

Please Login or Register  to view this content.

[gherzberg]

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.