Excel VB Macro For Moving Cells

[EBox]

Hello:

I am pretty new to VB with Excel, and I'm hoping someone can help with write some code for something which is probably pretty easy for some of you.

I have a series of Excel files with cell values in a certain layout, and I really just want to create a macro that will "transpose" the values.

Its hard to descibe the layout, but the "headers" are in row 3, and the values follow in rows below (each file has different # of rows). What I need to do is extract a certain block of question headers, starting with the one column header that includes the text "Please", through the last one. To the right of each question in the block are names, with scores to that question in rows below. See below for the sample layout:

A B C D E
3 Q1 Q2 Q3: Please John Jane
4 blah blah 5 4
5 blah blah 4 3
6 blah blah 4 4

What I would like to do is move them to a new sheet with a layout like:

A B C
1 Please John 5
2 Please John 4
3 Please John 4
4 Please Jane 4
5 Please Jane 3
6 Please Jane 4 ......etc.

I have so much data that it takes forever to manually do this, so if anyone can offer some coding help, it would be so very much appreciated by this newbie.

Thanks,
EBox

 

[RoyVidar]

If you are doing this through Excel VBA, then I'd think forum707 (Office VBA) is probably a more appropriate forum.

When working with Excel, you can just turn on the macro recorder when doing the actual stuff.

I guess you could pretty much record the actions when doing first a find from the edit menu (finding "please"), then highlight the necessary area (ctrl+shift+downarrow+rightarrow?), then copy, then in a another worksheet, select paste special from the edit menu, click transpose and OK ...

If this is performed from VB, then it should be a matter of cleaning up that code, prefixing it with the relevant excel object variables, I think.

Roy-Vidar