gitmaster
08-25-2010, 07:56 AM
This might be an advanced macro, or it might not even be able to be done, but here it goes.:think:
I have a large spreadsheet with lots of rows and columns. The first two columns have numbers, the rest of the columns have y/n for options. I have filters on those for different scenarios. Once I select all my filters that I want, I would like a macro to copy specific information to a new page.
In column 1, there are 6 possibilities: 10, 20, 30, 40, 50, or 60.
In column 2, there are lots of possibilites, most are from 0010 to 6150.
So for instance, column 1 starts at 10 and column 2 starts at 0010. Column 1 stays at 10 until column 2 reaches 6150, then column 1 goes to 20 and column 2 starts back at 10, and so forth.
Example: (where you see ... that's just to save space for this example.)
10 10
10 20
... ...
10 6140
10 6150
20 10
20 20
... ...
20 3110
20 3120
30 10
30 20
After I select my filters, obviously there will be gaps in the numbers. So instead of going from 0010 to 0100, it might go 0010-0030 then 0050-0080, then 0100.
What I want to do is copy the data onto a seperate sheet with specific guidelines, and this is where it gets tricky.
I want the macro to go down column two and copy the first number is comes across ( in this case, 0010) then paste it on sheet 2. Go back to sheet 1 and copy the number before the gap (in this case, 0030) and skip everything in between. Paste this on sheet 2 to the right of the first number. Go back to sheet 1 and copy the number after the gap (0050) back to sheet 2 and paste to the right. I want to continue on sheet 1 alternating between before and after the gap.
Now, to get even more complicated.
When pasting, I don't want any more than 8 columns out. So in the example above, it would read:
0010 0030 0050 0080 0100 0100
Once it hits the 8th column, I want it to go down 1 row and start over, pasting one column at a time until it hits row 8 again
I also would like it to jump to a new row when column 1 on sheet 1 changes values. So when it goes from 10 to 20, on sheet 2 it should start a new row to paste too.
I also would like the macro to completely stop when it hits a blank value.
Something tells me this is very complicated, and I'm not sure it can even be done.
:banghead:
I have a large spreadsheet with lots of rows and columns. The first two columns have numbers, the rest of the columns have y/n for options. I have filters on those for different scenarios. Once I select all my filters that I want, I would like a macro to copy specific information to a new page.
In column 1, there are 6 possibilities: 10, 20, 30, 40, 50, or 60.
In column 2, there are lots of possibilites, most are from 0010 to 6150.
So for instance, column 1 starts at 10 and column 2 starts at 0010. Column 1 stays at 10 until column 2 reaches 6150, then column 1 goes to 20 and column 2 starts back at 10, and so forth.
Example: (where you see ... that's just to save space for this example.)
10 10
10 20
... ...
10 6140
10 6150
20 10
20 20
... ...
20 3110
20 3120
30 10
30 20
After I select my filters, obviously there will be gaps in the numbers. So instead of going from 0010 to 0100, it might go 0010-0030 then 0050-0080, then 0100.
What I want to do is copy the data onto a seperate sheet with specific guidelines, and this is where it gets tricky.
I want the macro to go down column two and copy the first number is comes across ( in this case, 0010) then paste it on sheet 2. Go back to sheet 1 and copy the number before the gap (in this case, 0030) and skip everything in between. Paste this on sheet 2 to the right of the first number. Go back to sheet 1 and copy the number after the gap (0050) back to sheet 2 and paste to the right. I want to continue on sheet 1 alternating between before and after the gap.
Now, to get even more complicated.
When pasting, I don't want any more than 8 columns out. So in the example above, it would read:
0010 0030 0050 0080 0100 0100
Once it hits the 8th column, I want it to go down 1 row and start over, pasting one column at a time until it hits row 8 again
I also would like it to jump to a new row when column 1 on sheet 1 changes values. So when it goes from 10 to 20, on sheet 2 it should start a new row to paste too.
I also would like the macro to completely stop when it hits a blank value.
Something tells me this is very complicated, and I'm not sure it can even be done.
:banghead: