Copy selection by colour to another workbook

[durandal05]

I have this code:

Please Login or Register  to view this content.

which works, copies selected cells, paste them to another workbook, and highlight the originals as a colour

What i'd like is code that selects the rows from the list automatically, in groups of ten
( i posted some pseudo code here but the mod deleted it )


i know one of you wizards could probably knock it out in 5 second flat!
thanks for any help

[Richard Buttrey]

Hi,

There was no pseudo code incidentally

If you're going to select the rows in groups of 10 in a looping macro, then by definition you're going to select all the rows eventually. Why not just select the whole lot and copy in one go?

I must be missing some subtlety. Are you perhaps wanting a different colour applied for each group of 10? Can you explain a little further please.

Rgds

[durandal05]

maybe i edited out the pseudo-code by mistake
it's like this:

For each row in used range
If colorindex <> 50 and value <>"" then
copy row
.colorindex = 50
pasteto destination
End If
Loop until ten rows have been copied

imagine a big color coded list, all the white rows need to be dealt with
i'll have a button, you press it, and the first ten available rows are copied to another worksheet, and highlighted to indicate they've been dealt with

they need to go in batches of ten (maximum) -- have to print them out on forms, you see

i've given up trying to 'select' the rows in ten, rather just transfer them one by one to a temporary worksheet until ten is reached, but the code has got a long way to go

Please Login or Register  to view this content.

thanks for having a look
regards
Andrew

[durandal05]

i think what i need is a counter than increases towards ten only when a row is applicable, and loop until the counter reaches ten

and on the CopySelected macro, it needs to overwrite the previous temporary data, not just add onto the list, although i suppose i can handly that easily enough, just get the form-building macro to clear it

[durandal05]

so i have the loop going now...

Please Login or Register  to view this content.

only problem is, if the list runs out and there's only say 5 available rows, won't it crash? how do if get round that...

[durandal05]

ok i've sorted that loop so it stops once there's been a lot of blank rows
but the other loop kicks up an error message which i don't know how to solve

(one loop highlights appropriate rows, the other takes highlighted rows and transfers them)

i've marked it with a comment. error 1004 method 'offset' of property 'range' failed. wierd thing is the action was still successful in the background

Please Login or Register  to view this content.

[Richard Buttrey]

Hi Andrew,

Can you attach the workbook, or at least a representative sample, so that I can see the requirement in context.

Regards,

[durandal05]

hi,
sorry for the delay, i was cutting the hedge

here it is, attached
sheet1 would be the list i want to work from
sheet3 would be the temporary space the rows are copied to
sheet2 would be the form for printing (it's just a blank sheet at the moment though)

first macro (command + option + j) should highlight ten rows in amber
second macro (command + option + k)should transfer amber rows to sheet3 and change them to green
third macro (command + option + l) clears sheet3 and moves the data onto sheet2 (the form)

ultimately the macros will trigger each other. once i have them working

second macro is the one that doesn't work properly

[durandal05]

d'oh!
at some point i accidentally deleted my counter line
the copyselected macro should have had the line
mycount2 = mycount2 + 1

i can see the error was generated by the macro reaching row 64553 or whatever it is and being unable to offset one more row
now the loop stops it

so... i think i've got this under control now, just need to test it with a few different permutations

[durandal05]

right, it all seems to work now
there's just one thing i hope you can help me with
as you can see, i use a statement like

Please Login or Register  to view this content.

a few times.

is it possible to change the colour of just a few columns within the row?
instead of 'entire row' i'd like it to be just the cells in the first 3 columns, for instance

something like

selection.firstcolumn:thirdcolumn.colorindex=40

know how to do this?

regards
Andrew

[shg]

Please Login or Register  to view this content.

[durandal05]

aah, beautiful

thanks a lot!

[Richard Buttrey]

Hi Andrew,

(Hope the hedge feels better for it)

Just to clarify and a question

Are you always wanting to copy blocks of 10 rows where the rows are contiguous (i.e. eventually all the rows are copied), or do you start with rows which are already coloured green, say row 2, 5, 7, and you want to copy the 10 rows 1,3,4,6,8,9,10,11,12,14.

I suppose the question I'm asking is do you start with all rows uncoloured on sheet1?

If so it's considerably simpler just to select whole blocks of 10 without testing each individual row.

That being the case why do you want to copy first to sheet3, then to the form on sheet2? I'm assuming you want to print each batch of 10 rows or something like that. In which case why bother with sheet2 at all, why not print from sheet3 directly.

I'm assuming the ultimate requirement simply stated is to print blocks of 10 rows from sheet1, colouring them amber when that's been done. In which case the following simplified macro should do it. I've assumed that rows 1:10 on sheet 3 are preformatted green, hence there's no need for a VBA instruction to do this, which only slows things down.

Please Login or Register  to view this content.

Even easier of course would be to print directly from sheet1 without bothering withe either sheets 2 or 3

Incidentally your error message is because you are not altering the mycount2 variable within the loop, and you end up at A65536 with a .Offset row of 1 which XL can;t deal with.

If the rows you're wanting to deal with are not blocks of contiguous rows - see my earlier Q. then post back since the macro will need modifying.

Regards

[durandal05]

Hi Richard,
This macro you have looks rather smart.
Unfortunately your suspicions were correct... the rows are not necessarily contiguous. Basically one user will be changing rows at random positions in the list to a new colour (I was thinking amber), and then another user will be pressing a button whenever they desire that will generate a form (sheet2 will be a template for the form) with ten rows worth of data on it, data from the amber rows. Those ten rows will be made green on the original list, so they are not processed again.

I know it sounds strange having forms with ten etc. but you know what offices/businesses are like!

So, to your question - why do I have Sheet 3 as an intermediary step between the list and the form?

- i guess my original thinking was that since i can't (or don't know how to) add rows to the selected area (and you can't copy non-contiguous ranges anyway...), i'd need a temporary space to line up the rows in a solid block of ten. Perhaps i could just deposit them straight onto the form one by one, come to think of it...

once again, thanks for your help.
i think i have it solved but there's further subtleties i need to add, where i might run into trouble
so, i'm still very interested in your thoughts but if you have other cases to attend to i understand

[durandal05]

i may as well add that ultimately there will be two forms
and depending on the number at the start of each row, i'll be putting the data on the one form or the other

so, what i'd need for this, is the expression to detect the first number in the first cell of the row. So

If Cells(activecell.row,"A").value = 8 Then...

is not enough, as the cell will have several digits. What's the expression for the value of the first character, if it exists? Anyone know?

edit: actually i think i've got this one. using the MID function a macro activates and uses a different colour when the cell is double clicked, depending on the first digit of the row

Please Login or Register  to view this content.