Copy and transpose tables from different sheets into a single sheet

does the data contain formulas?

try this then

Please Login or Register  to view this content.

keep the formatting of the data

^^ this code only keeps number format
if you want cell format that would require tweaking of code

Please Login or Register  to view this content.

^This does cell formatting as well as number formats and values now

Hi,

this is really really awesome!

I was wondering if one could include a few things:

1) The formatting in the sheet summary is lost. Is it possible to import the original formatting of the untransposed blocks?

2) Instead of one line between the data blocks in the sheet summary, I get a random number of blank rows (between 40 and 70).

3) The workbook contains also sheets which I do not want to combine into the summary sheet. Is there a way to tell the macro that it should only combine the selected sheets?

Edit: I did not see your second post before posting mine.

1> 2nd code does that

2> yes though the determination of what is blank is up to you
i just made it copy all 76 columns regardless
do you want to base it on ROW 2?

3> you need to tell me specifically what is in your workbook in order to provide an accurate response
is there many more sheets or just one or two?
if one or two...add more ifs to check sheet name
if many...i suggest you tell me what the sheet names of what you want to include instead
try to find a pattern or something

i think this is what you want from point 2

Please Login or Register  to view this content.

this is the only line i changed

i = ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1 'determine next row based on column A of summary

on 1): with your new code, we also have the formatting - great! Is it actually possible to NOT include the cell border lines?

this is rather hard to do...as you are transposing the data it doesnt really grab the appriopate cell borders (unless its border lines that wraps for every individual cell)
manually do a set yourself and you will see what i mean

maybe easier to replicate the formatting rather than keep it but without actually seeing a sample i can only guess

On 3): I have a pattern. The first sheet is "Summary" and then the 12 sheets to be combined follow.

changed the how the code loops to accommodate this pattern

using the principle of worksheet index instead of just looping all worksheets

Please Login or Register  to view this content.

here is the issue with problem 1 in example
you can clearly see it does not give you the results you want

hmm problem 2 again...i will need to revisit again next time

its time to go home for today
maybe someone else can pick up on it otherwise ill be back on monday

Hi!

Thank you very much for everything so far! I think an easy way to solve the problem, would be to delete the blank rows in "Summary" between the data blocks up to one.

Is this possible?

Edit: Yes: https://www.excelforum.com/excel-pro...ta-blocks.html

not sure if this is resolved or not?