How to repeat an instruction instead of copying it hundreds of times?

[Hellion]

Hello guys. My name is Ricardo and I'm totally new to VB.
I'm trying to convert a cross tab into a flat list like this:

Please Login or Register  to view this content.

As I have 140 Cost Centres and 6 Natures I have 840 repetitions and then excluding the zeros.

My basic skills only allow me to write it manually such as:

Please Login or Register  to view this content.

and so on.

How can I make it more automatic, please?

Thank you in advance.

Ricardo

[DBY]

Hello Ricardo welcome to the forum.

Take a look at the attached example. Does this do as required?

DBY

[MickG]

Try this:-
Run code on Data sheet for Results on sheet2

Please Login or Register  to view this content.

Regards Mick

[Hellion]

Dear BDY and MickG.

Thank you very much for the warm welcoming to the forum. I have to say I never though I would get such a quick reply.
In a sense, it just made me to feel more insignificant and realize how much I have to learn.

The 2 ways you showed me are very elegant and work perfectly for the example I gave but because I wasn't too specific (I suppose I can evoke my first timer mistake now),
I'm having a hard time to adapt it for my needs even having experimented changing the codes many times.

My cross table is in sheet4 called "CC GA", range D7:J146 (headings not included, just sheer data) where Cost Centres are on column D, and Salary, Fuel, Phone, Vehicle, Petty, Others on columns E to J, respectively.
I want to transpose the data to sheet10 called "Accounts Export", range C9:E(whatever it takes).

Could you guys explain to me how to do it in both ways as a matter of education.


Nice regards,

[MickG]

Try this:-

Please Login or Register  to view this content.

Regards Mick

[DBY]

Here's my code with another example file amended to simulate your description of data.

DBY

[Hellion]

Dear MickG and DBY,
You guys rock.

Both ways worked brilliantly and as I actually have two ranges to be copied to the same sheet side by side I'm simply using both codes.
I only introduced a line to activate the Accounts Export sheet in the beginning of the code as I forgot to say I wanted to execute it from the Accounts Export sheet (no excuses this time),
another line to unprotect the sheet (this one I decided later) and two more lines before ending the code to activate the Accounts Export sheet again and to protect it again.

Also, by comparing the latter codes to the ones you sent me previously I was able to understand better how this methods work. Is the case to say that I have a lot to read, I guess...

Thank you very much,


Ricardo

[DBY]

Glad we could help. Thanks for the rep mark.

[MickG]

You're most welcome, Thank you for your reply.