Copy/Paste transposing formatted cells every two rows

padbergus

New Member
Joined
Jun 29, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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 1Type 2
MondayC
MondayC
TuesdayAC
TuesdayBC
WednesdayAC
WednesdayBC
ThursdayAC
ThursdayBC
FridayC
FridayC

This table has to be formatted to a table like this:

MondayTuesdayWednesdayThursdayFriday
Type 1AAA
BBB
Type 2CCCCC
CCCCC

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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,475
Office Version
  1. 365
Platform
  1. Windows
Does this presentation work for you? If yes, then I will describe how to get there once you have responded.

Book8
EFGHIJ
2Types by DayColumn1
3AttributeFridayMondayThursdayTuesdayWednesday
4Type 1A, BA, BA, B
5Type 2C, CC, CC, CC, CC, C
Sheet2
 

padbergus

New Member
Joined
Jun 29, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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
CDEF
30TYPE 1TYPE 2TYPE 3
31Montaguser 1
32Montagtodo 1
33Dienstaguser 1user 2
34Dienstagtodo 1todo 2
35Mittwochuser 1user 2
36Mittwochtodo 1todo 2
37Donnerstaguser 1user 2
38Donnerstagtodo 1todo 2
39Freitaguser 1
40Freitagtodo 1
VW
Cell Formulas
RangeFormula
C31:C40C31=B31



MontagDienstagMittwochDonnerstagFreitag
01.02.202102.02.202103.02.202104.02.202105.02.2021
TYPE 1user 1user 1user 1user 1user 1
todo 1todo 1todo 1todo 1todo 1
TYPE 2user 2user 2user 2
todo 2todo 2todo 2
TYPE 3


Thank you again for your time and help.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,475
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,141,479
Messages
5,706,621
Members
421,460
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top