selectively copy and transpose data using VBA

**cobbcito** · 02-04-2011, 11:08 AM

I've tried making this work by finding and copying VBA code off the net...but I think my needs are too specific and my skills are too unrefined to modify what I find appropriately.

Basically I regularly receive reports in a very inconvenient format and I want to write a simple macro to parse and rearrange the data into a more user friendly orientation. Basically I submit a number of biological samples to a chemist (anywhere from 1 to 150) and he sends me back a report with Avg, Stddev, and %RSD data for a number of different elements in that sample. The problem is his report uses dozens of rows to report the data from one sample, and I want to rearrange the data so that every row is a sample and every column is an element generating one worksheet with a matrix of means, another with a matrix of SD's and a third with a matrix of %RSDs.

Attached is a file where the "Raw Data" tab shows the report I'm getting (with 150 samples, and 49 elements). I was able to use formulas to generate worksheets arranged the way I want, but the problem is the reports I receive often have a different number of elements (not always 49) which in turn changes the spacing in the report between samples so my formula's reference the wrong cell.

I hope that makes sense. Looking at the file it should be clear. Any help at all would be greatly appreciated!

**JBeaucaire** · 02-04-2011, 12:41 PM

Give this a whirl:

Please Login or Register  to view this content.

**cobbcito** · 02-04-2011, 05:00 PM

Honestly...that was like magic! I've even tried varying the input file with differing numbers of samples and elements and it still worked like a charm!

So...I just have one more follow up question...I've seen some Macros that encode a button right on the worksheet that will enable the macro without the need to go through the menu bar. Is that something simple to do? It would make it substantially easier for some of the less excel saavy members of my team to utilize.

**JBeaucaire** · 02-04-2011, 08:11 PM

Forms Toolbar

Button icon - draw button on sheet.
Macro popup window appears, select the Reorganize macro from the list.
Righ-click on the button and edit the button text.

Right-click to move button around.

**cobbcito** · 02-05-2011, 11:10 PM

Thank you so much! This is the first time I've ever posted in a forum like this, and your efforts have saved me days of work. Much appreciated!

**pike** · 02-05-2011, 11:17 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**cobbcito** · 02-07-2011, 12:55 PM

JBeauclaire,

If you don't mind my bugging you (no pun intended) just one more time, I thought I would mention that when I loaded a different report on to re-arrange it this morning, at the end of the process I got the following error:

"run-time error '1004'

That command cannot be used on multiple selections."

When I hit debug...it referenced the following line of code:

Please Login or Register  to view this content.

under the 'create reports' heading. That being said, it still rearranged all the data with no problem, and it created a new worksheet called "Raw Data(2)." It would just be nice not to get the error every time.

Is there anything that can be done for this? In case you need it, attached is one of the other reports I tried that generates this error. Thanks so much for your help!

**cobbcito** · 02-07-2011, 01:16 PM

After playing around with it a bit more, and looking at the differences between the reports that generate the error and the sample one I sent you, I think I see the problem.

At the end of the reports (see Bag#3.xls from my previous post) there is a line at the very end after the last sample that says:

Published: 2/4/2015 2:48:08 PM Page -1 of 1

Somehow, this line was not included in the original template I posted to the forum. Is there a way to tell the code to forget about this line? Or at the very least not to tell me about the error that this line is causing? Thanks again for your help.

**JBeaucaire** · 02-07-2011, 09:41 PM

This will eliminate that problem. Also, I added some nice formatting at the end to dress up your results sheets and make them easier to read as you scroll down lengthy data.

Please Login or Register  to view this content.

**cobbcito** · 02-08-2011, 12:01 AM

I really appreciate the effort your putting into this for me. When I run this next bit of code, I get the following error:

Run-time error: '1004':
Method 'FreezePanes' of object 'Window' failed

When I click debug...it highlights this piece of code, presumably in the section that does some of the formatting you referred to:

Please Login or Register  to view this content.

Any thoughts? Attached is the file with the macro for your reference. I didn't include the output worksheets for file size reasons. It produced the same output I described in my previous post (complete with the extra 'Raw Data' worksheet).

**JBeaucaire** · 02-08-2011, 01:15 PM

Hehe, I opened your attached sheet, clicked the button and "bing", Worked fine.

As a test:
1) open a new workbook.
2) Turn on the macro recorder
3) Select cell B2 on the active sheet
4) Select Window > Freeze Panes
5) Turn off the recorder.

Compare your recorded freeze panes code with what I gave you. Should be the same.

**cobbcito** · 02-08-2011, 05:19 PM

Okay...so for some reason the Freeze Panes option is unavailable in the Page Layout view on my machine....I have no idea why that would be the case, but based on a quick google search it's not unique. So that's why it kept giving me the error message.

I used the "record macro..." option to figure out how to change the worksheets to Normal view and added a line just above the Freeze Panes line and it worked like a charm! I'm actually really proud I figured that out on my own!

Thank you so much for your help, and again when I was having trouble making it work!

**cobbcito** · 02-10-2011, 12:55 PM

Okay....so I may be over-staying my welcome by now when it comes to modifications of this file...and if I am, just let me know! But based on some feedback I've gotten from some other members of my team I wanted to know if it's possible to make the following modifications?:

1) St Dev and %RSD are really different metrics of the same thing, so the St Dev worksheet is unnecessary and can be eliminated.

2) Is it possible to create one last worksheet called "Global Report" or something that combines the data from the Means Report and the %RSD Report in the same spreadsheet? Basically putting the Mean value and the %RSD value for each element in adjacent columns? I've attached an example of what I'm thinking with only 5 samples and 4 elements (for size purposes) in case I'm not describing it very well.

If you have time, and can make those modifications....I'd be forever grateful!

**JBeaucaire** · 02-10-2011, 09:40 PM

I'm sure this could be done directly and shorter, but I just built on the macro already created to rearrange the data already collected... heh.

Please Login or Register  to view this content.

**cobbcito** · 02-11-2011, 09:44 AM

So...I was hoping we could keep the 'Mean Report' and the '%RSD Report' in addition to the 'Global Report.' Sorry if that wasn't clear. The data is valuable both on its own as well as side by side.

**JBeaucaire** · 02-11-2011, 01:48 PM

Try this:

Please Login or Register  to view this content.

**cobbcito** · 02-12-2011, 11:26 PM

Again...I can't thank you enough. It worked flawlessly and reflects perfectly the feedback I had received from my team. Thank you again.

selectively copy and transpose data using VBA

LinkBack

Thread Tools

Rate This Thread

Display

selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Re: selectively copy and transpose data using VBA

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions