Transpose Every Row From Multiple Sheet Into To One Sheet Into Multiple Column

[Jhon Mustofa]

hi expert..
the following problem, i would hope someone would help me out to solve this problem:
i have a workbook that contains several sheet with random name sheet. For each sheet have the same format but different data.
i want to transpose every 22 or n rows from multiple sheet and multiple column to one sheet into multiple columns. Then my data contains merge cell and i want keep it. The following cell below that i want to transpose 12 cells with criteria like this:
start from transpose cell C3,C4,C5,C7,C8,C11,C12,E9,E11,E12,C15 and C21 (sheet "lap-1)
start from transpose cell C25,C26,C27,C29,C30,C33,C34,E31,E33,E34,C37 and C43 (sheet "lap-1)
etc...
with interval row is 22 row and repeated occurring for the other sheet

how to transpose to other sheet e.g. "sheet "destination" or other sheet name.
for easy i highlighted color cell.
here attachment file

any help, greatly appreciated
john m

[Mumps1]

Before trying the macro below, you will have to unmerge all the cells containing "Nama Bangunan Air" in column A of all the worksheets. You should avoid using merged cells at all cost because they almost always give macros problems. Also start with a blank "destination" sheet.

Please Login or Register  to view this content.

[Mumps1]

This should work without unmerging the cells.

Please Login or Register  to view this content.

[Jhon Mustofa]

hi Mumps1....thank yo so much.
Working well!!!!

[Mumps1]

You are very welcome.