Hello everybody,
normally I am working with Python, but I have a small VBA project in which I have to copy cell values and format (background color) to a new sheet in a transposed way, but for me the problem is that 2 cells "belong" together, as you can see in the following table, there are always 2 values per weekday.
This table has to be formatted to a table like this:
So far I have a working code:
For y = 0 To 4 'Day Count
For i = 0 To TypeCount- 1 'Type Count
Worksheets(1).Range(Cells(StartReadRow+ (2 * y), StartReadColumn+ i), Cells(StartReadRow+ 1 + (2 * y), StartReadColumn+ i)).Copy Destination:=Worksheets(2).Cells(StartWriteRow+ (2 * i), StartWriteColumn+ y + 1)
Next
Next
But I have also several sheets and more types in my document, so it can be a little slow. Is there a way to speed this code up? Perhaps select all weekday values for a single type or even all weekdays values for all type values and paste it to another sheet in the above format (also with cell background colors). I tried different things with pasteSpecial: Transpose = True, but I could not get it to work..
Any hint is very appreciated, thank you for your time.
normally I am working with Python, but I have a small VBA project in which I have to copy cell values and format (background color) to a new sheet in a transposed way, but for me the problem is that 2 cells "belong" together, as you can see in the following table, there are always 2 values per weekday.
Type 1 | Type 2 | ||
Monday | C | ||
Monday | C | ||
Tuesday | A | C | |
Tuesday | B | C | |
Wednesday | A | C | |
Wednesday | B | C | |
Thursday | A | C | |
Thursday | B | C | |
Friday | C | ||
Friday | C |
This table has to be formatted to a table like this:
Monday | Tuesday | Wednesday | Thursday | Friday | |
Type 1 | A | A | A | ||
B | B | B | |||
Type 2 | C | C | C | C | C |
C | C | C | C | C |
So far I have a working code:
For y = 0 To 4 'Day Count
For i = 0 To TypeCount- 1 'Type Count
Worksheets(1).Range(Cells(StartReadRow+ (2 * y), StartReadColumn+ i), Cells(StartReadRow+ 1 + (2 * y), StartReadColumn+ i)).Copy Destination:=Worksheets(2).Cells(StartWriteRow+ (2 * i), StartWriteColumn+ y + 1)
Next
Next
But I have also several sheets and more types in my document, so it can be a little slow. Is there a way to speed this code up? Perhaps select all weekday values for a single type or even all weekdays values for all type values and paste it to another sheet in the above format (also with cell background colors). I tried different things with pasteSpecial: Transpose = True, but I could not get it to work..
Any hint is very appreciated, thank you for your time.