VBA code to transpose same cell range from all worksheets of a workbook on a new worksheet

[zicitron]

Hi all,

I'm using Excel 2013. I would be grateful if someone could help with a VBA code that will perform the following task:

I have a workbook which contains 10 worksheets, each with a specific name. I need to transpose the data located in cells B4 to M4 of each worksheet into a new worksheet. Also, it would be great if the VBA code could fill the name of the specific worksheet alongside each transposed cell.

In other words, say, worksheet 1 is called "Europe", the transposed data (from B2 to M2) would have "France" in column B next to each cell of the new worksheet, followed by same principle for the Worksheet 2, and so on.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[zicitron]

Thanks John. Works partially as it just copy and paste the data but do not transpose it into a vertical format.

[JOHN H. DAVIS]

It would help to understand your layout if you could provide a sample with a before the macro is run and after. Try.

Please Login or Register  to view this content.

[zicitron]

Thanks again, John. You are right about including a sample for a better understanding of my needs. Please find attached a sample.

Worksheet named "new worksheet" is what I need as the final output of the VBA code. Thanks for looking into the issue again.

sample for excel forum.xlsx

[HaHoBe]

Hi, zicitron,

the sample provided differs from the description you posted - so any effort from our side will fail as we don´t know how your workbook looks like.

Maybe try:

Please Login or Register  to view this content.

You may comment out the code line to ClearContents if you want to keep the data.

Ciao,
Holger

[jolivanes]

Something like this might work also.
No sheet is added as in John's code. You can add that if you need/want to.

Please Login or Register  to view this content.

[HaHoBe]

Hi, jolivanes,

thanks for postiung - as I mixed up my code with Sheets (containing all sheets including charts and macros) and Worksheets and again am not able (and shoulddn´t alter) my post here are two adapeted codes:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger