Creating a report /copy,paste,transpose(no gaps).....HELP

[mikejcass]

Hello,

I am in need of help with some code.

I have a workbook that has multiple worksheets, some worksheets are used to gather data ie data is input by users. the data is then worked to form graphs tables etc, in a number of what I call data sheets. I have an upper management summary worksheet which is displayed in landscape, but what I have been asked to produce is a summary sheet with 5 to six headings. The data required to fill this summary sheet is on one of my data sheets, but what I need and can not figure out is:

1. Copy row 2 in data sheet which has data in every second cell, paste into summary sheet col A with no spaces.
2. Copy row 190 in data sheet which has data in every second cell, paste into summary sheet col B with no spaces.
3. Copy row 191 in data sheet which has data in every second cell, paste into summary sheet col C with no spaces.
4. Copy row 196 in data sheet which has data in every second cell, paste into summary sheet col D with no spaces.
5. Copy row 200 in data sheet which has data in every second cell, paste into summary sheet col E with no spaces.

The issues I am having are, I can record a macro to kind of do what I want, but the cells I am copying are fed from another sheet so when I paste special "Values" I loose the dynamics I require. (I need the summary sheet to update with the data sheet).

I really need help with this as i have spent a lot of time on it with no success so far. I am sure theres probably a really straight forward solution but for the life of me I cant figure it out.

M

 

[Sathish KV]

Hi,

Could you post a sample file

 

[Hui]

Mike

Firstly, Welcome to the Chandoo.org Forums

The attached file shows you the technique to transpose a row of numbers with gaps into a Column of numbers without gaps using formulas

 

[mikejcass]

thank you for both responses
I will upload sample prior to looking at suggested file,

thanks again in advance
M

tried file is too large, sorry Ill review the suggested file and revert if it works for me

 

[Hui]

You could try saving as an Excel Binary file type *.xlsb
That will be much smaller

 

[mikejcass]

I would really like to do this automatically using vba.

Attached is file showing a minor section of my requirements

M