Macro to reformat labels and values and ignore blanks

[RSW]

I have a lot of data in the format shown in Sheet 1, and I can't figure out how to set up a macro that would put it in a format like Sheet 2.

My issues are:

- I don't want to include any blank rows when moving the Sheet1 data.

- The totals on Sheet1, which come every set number of rows, should not be moved onto Sheet2. Totals on Sheet 2 should be calculated separately.

- For a given widget, all the Planning cells should be moved first, then all the Building cells, then all the Packing cells. Just no blank ones. Then the next widget should be moved onto.

I can't figure out how to record a macro that will do this correctly, given that there could be any number--even zero--of cells corresponding to each section.

Is there any way I can do this? Any advice to get me unstuck will be very much appreciated!

 

[Brianwarnock]

Recording a macro wont work you will have to write it from scratch. There are a number of loops to control the overall loop to end of data, the overall widget loop, the column within widget loop.

I might be able to have a look at the weekend.

Brian

 

[RSW]

Recording a macro wont work you will have to write it from scratch. There are a number of loops to control the overall loop to end of data, the overall widget loop, the column within widget loop.

I might be able to have a look at the weekend.

Brian

I'd sure be grateful. I not only have absolutely no idea how to do this, but absolutely no idea where to find an example of anything like it!

 

[Brianwarnock]

Hi, you might like to test the attached. I did modify your data slighty to test for a blank column, widget3.

You will note the use of GoTo s in the code, which will upset purists but my mantra is
" GoTo is banned by idiots and abused by fools"

I hope I haven't abused them, I believe it makes the code simpler to understand and maintain as opposed to convoluted Do Loops, I have of course used Do Loops.

Brian

 

[Brianwarnock]

Noticed minor formatting error, corrected in attached

Brian

 

[RSW]

Thank you SO much, Brian.

I'm about to go over the code and make sure I understand what it's doing. I've never done anything remotely like this before.

My example was a simplified version of the much larger spreadsheet I'm working with, but I'm pretty certain the same type of logic will be used.

Thanks again!!