Copy/Paste transposing formatted cells every two rows

[padbergus]

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.

	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.

 

[mumps]

But I have also several sheets and more types in my document

Can you please clarify what you mean by this? Do you want to perform the copy/paste on several sheets? Also, which cells will have the background color? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your data sheet and the sheet showing the desired result. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[alansidman]

Does this presentation work for you? If yes, then I will describe how to get there once you have responded.

Book8
	E	F	G	H	I	J
2	Types by Day	Column1
3	Attribute	Friday	Monday	Thursday	Tuesday	Wednesday
4	Type 1			A, B	A, B	A, B
5	Type 2	C, C	C, C	C, C	C, C	C, C
Sheet2

 

[padbergus]

Hello,

thank you both for your answers and efforts.
@alansidman: unfortunately this presentation is not sufficient for me, as the output format is defined by our internal customer, but thank you!

@mumps: with the mentioning of the more sheets and types I just wanted to express, that there are multiple tables with more rows (called types in my example) to be copied from which leads to the slow processing, but we can focus on the algorithm which is needed to copy one table from one sheet to another table in another sheet.

I try to make it more clear with XL2BB:

example.xlsm
	C	D	E	F
30		TYPE 1	TYPE 2	TYPE 3
31	Montag	user 1
32	Montag	todo 1
33	Dienstag	user 1	user 2
34	Dienstag	todo 1	todo 2
35	Mittwoch	user 1	user 2
36	Mittwoch	todo 1	todo 2
37	Donnerstag	user 1	user 2
38	Donnerstag	todo 1	todo 2
39	Freitag	user 1
40	Freitag	todo 1
VW

Cell Formulas
Range	Formula
C31:C40	C31	=B31

	Montag	Dienstag	Mittwoch	Donnerstag	Freitag
	01.02.2021	02.02.2021	03.02.2021	04.02.2021	05.02.2021
TYPE 1	user 1	user 1	user 1	user 1	user 1
	todo 1	todo 1	todo 1	todo 1	todo 1
TYPE 2		user 2	user 2	user 2
		todo 2	todo 2	todo 2
TYPE 3

Thank you again for your time and help.

 

[alansidman]

In your post, you have a formula in column C that is dependent on column B. When importing to Excel, there is no column B, therefore the days of the week are excluded in Column C. Please reload including column B. Additionally, there are no dates in your XL2BB posting to put into the Pivot Table. Would those be in the missing Column A?