Manipulating certain rows only

[Shaz]

I am trying to set up a running total of expenses etc, where my first sheet
is a combined running totals, from 4 different accounts, so the rows are
colour coded accordingly. I then have monthly sheets, where I want to break
down of the running totals according to the account.

PROBLEM -

Is there anyway of copying only certain rows, such as, all the blue text
rows, or the red text rows? I have tried to select the relevant rows, by
pressing control and the relevant coloured text for each bank account, but
when it comes to copying, it will not allow my to copy multiple rows.
Copying each individual row is WAY too tedious, there must be a quicker way!
I even tried hiding the colours I didn't want, but when I press copy, it
copies the whole lot across, not just the visible colour!

Does this make sense, or am I trying to make my life way too complicated??????

My aim is to have a running total on the one sheet, and then I can also
check on the individual movements of account transactions and see the
balance, if that makes sense??

 

[Gord Dibben]

Other than the colored rows there must be another method of defining the 4
accounts.

Have you tried Data>Filter>Autofilter on account names or account numbers?

You can then copy the visible rows to another sheet.


Gord Dibben MS Excel MVP

 

[Shaz]

Good thought, but that isn't going to work, unless you are able to filter
according to colours!

 

[Gord Dibben]

From your response I take it there is no other method of defining each account.

Unfortunate. You will have to use VBA to enter the index number of of each
colored row.

Then you could filter on that column with the numbers.

See Chip Pearson's site for functions to return the index number of a color.

http://www.cpearson.com/excel/colors.htm

You would place his Cellcolorindex function into a mofule in your workbook.

Then in a helper column enter =Cellcolorindex(cellref)

Filter on that column.


Gord

 

[Shaz]

Thanks Gord.....I have had a look at that website and it looks way too
complicated for me :-(

How else would you suggest I define each account? I have only changed the
text colour as that was the easiest way at the time, but don't know how else
I can do it.....

Filters don't seem to work though, because I added an add-in and was able to
filter between the colours beautifully.....which is PERFECT.....but then am
back to square one when I try and copy the data across.....it copies the
entire range, not just the filtered section......so if there was a solution
to only copying/pasting the filtered section......then my problem would be
solved.....

 

[Shaz]

Well....I don't know WHAT I was going wrong last time....but suddenly when I
filter and copy and paste, it is now only pasting the contents I have
filtered.....have no idea why it wasn't working earlier......strange!