FAO Excel Bods - Hard Problem :o0

[The Boosh!]

Reet here we go. This is what i am looking at on the most basic level:

What i want to achieve is this.

When i press a button (named button in the pic )

I want a macro/something to take the data from source of data and place it in a table (the table is at the top of the image with data written in a row).

Now that i can do fine in a macro its the next bit i can't get. Because im wanting a moving total ( its for peoples stats at work) every month, il press the button, the macro will put the data in the table, then when i press the button again it will put the data in the row below, then next month the row below that etc etc.

am i being dumb/answer really obvious?

I hope i have been clear enough

thanks for your help

 

[AK]

Not a clue.

Macros FTL tho.

 

[The Boosh!]

yup. mega ball ache mate.

i HATE excel

 

[James.f]

i dont understand, im brilliant with excel tho,

 

[AK]

LOL i'm normally pretty good at excel, but the minute people start talking about macros i'm out lol!

 

[The Boosh!]

i dont understand, im brilliant with excel tho,

basically when i press a button i want data to move from one place to another and add a new row each time (so the data doesn't just keep over writing)

 

[iain187]

have a cell count the rows
make you macro look at the count and +1, then insert new data where appropriate?

 

[The Boosh!]

have a cell count the rows
make you macro look at the count and +1, then insert new data where appropriate?

soundsl like it may work but i haven't a clue what your on about sorry mate lol

 

[James.f]

i think what hes tryin to say is, from a emplty cell, hold the mouse down and highlight the cells with sum's in them, then press Autosum at the top, (looks like a Z)

 

[iain187]

soundsl like it may work but i haven't a clue what your on about sorry mate lol

how far have you got with ur macro so far?

 

[The Boosh!]

iv got it to copy the shiz and move it to where i want it to go. the bit im struggaling with is to get it to move the next lot of data to the next row.

 

[iain187]

out of the 'next lot' does the macro pick up which is the right data to move?

is it just putting it in the wrong place when you tell it to add to the spreadsheet?

 

[The Boosh!]

yeh thats right mate...

the source data never moves it just changes ever month as it gets cleared off and starts again.

its getting it to put it in the right place when i press the button. every time i press it it over writes the last lot of data. i dont want it to do that i want it to put it on the row below each time

 

[iain187]

ok i think I am with you now.

I'm not to hot on macros but I know what you have to do (haha)

put a cell at the top of your table and do =COUNTA(range)

this will give you a reference of how many rows your table currently takes up that you can use in your macro.

then edit your macro to insert the data at row =COUNTA(range)+1

 

[The Boosh!]

ok i think I am with you now.

I'm not to hot on macros but I know what you have to do (haha)

put a cell at the top of your table and do =COUNTA(range)

this will give you a reference of how many rows your table currently takes up that you can use in your macro.

then edit your macro to insert the data at row =COUNTA(range)+1

'kin ell Ian(?) you know your stuff man!

Il give it a try on the proper spreadsheet at work and bump this if i have any trouble Cheers mate

 

[iain187]

iain :rasp:

ps i doubt that =counta() formula will change after you press the button. So what will happen is if you have 10 rows to start, the counta will say 10 even after inserting an extra 5 (e.g.) to the bottom.

therefore your macro will insert new rows at row 11!

so you have two choices, you can either rewrite the counta formula each time before you press the button or you can get the macro to count; how many rows you have and how many rows you are inserting so it is counting the correct amount of records.

(i can explain that better once you get this first bit going if that is confusing!)

 

[The Boosh!]

i understand what you mean...i think?

you basically mean, start the macro from scratch encorporating that counta formula ?

 

[iain187]

well as your table gets bigger you will need the counta to count more rows or it will go back to overwriting half way down itself.. the best way is to get the macro to do it for you

 

[The Boosh!]

well to be honest we only need the data for a year so thats 12 rows... Il give it a bash when i get into the office. may possibly pm you/bump this if i get major stuck

 

[iain187]

the other option is put the data somewhere else and copy and paste into you summary table i suppose

 

[The Boosh!]

now your boggling my mind lol

 

[Gray]

It is because you are using a Mac....

 

[The Boosh!]

It is because you are using a Mac....

Suck me off

 

[Gray]

Suck me off

Not agaaaain... I have only just got rid of your "Love juice" taste in my mouth, from this afternoon!!

 

[The Boosh!]

pmsl

although i will agree that mac is FTL with excel unless you know were everthing is.

Luckily this is for work which is on the proper office so il be able to find my way around a lot better

 

[Hutchie]

erm.... yeah i can make it do stage one but not add another row sorry dude

 

[The Boosh!]

cheers anyway hutchster