macro to put data in relevant groups

[swfarm]

hi guys

im looking for some help please to auto fill data into specific areas of spreadsheet based on the day

please note i have attached spreadsheet

the data in columns h:n are auto populated and sorted from another spreadhseet

so what i would like to do with a button is this ( if possible )
in h11:n16 there are 2 days that correspond to MONDAY
what i need the macro to do is move that data ( as per heading ) to mondays range which is b13:f22

column a can be ignored as this looks up column b and returns the account code

and also to do the same for corresponding days

can anyone help please?

[swfarm]

all help would be gratefully received please

[arlu1201]

Before copying the data would columns B:F be blank or would there be existing data?

Also, should the macro check if the number of rows for Monday are enough to hold the data or will they always be enough and we only need to copy paste?

If there is existing data in columns B:F, should it be deleted or should the data be copied below the existing data in columns B:F?

[swfarm]

Hi Arlu and many thanks for your reply

In answer to your questions
the cells between b13 and f77 will be blank

ideally the macro would check there is sufficient rows but if not insert rows to cover

many thanks

[arlu1201]

Would it be fine with you if the rows 13:77 are completely cleared (Days of the week also cleared) and the data from the right side is input freshly, after leaving about 2-3 rows between each day of the week?

[swfarm]

the days od the week are pretty important but if you have worked something out i will gladly appreciate it and take a look.
thank you

[arlu1201]

Also would it be fine with you if the days of the week in column I and the dates of the week in column A are spelt the same?

[swfarm]

Hi Arlu
Yes that would be fine

[arlu1201]

So should we consider that the data in column I would be like MONDAY, TUESDAY or would it be the other way round?

Sorry these are small questions but required for the code.

[swfarm]

Monday, tuesday, wednesday is fine

[arlu1201]

Try this code -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Vew | Macros
Select a macro in the list, and click the Run button

[swfarm]

doesnt do anything arlu

[apo]

Here's another..

Press the "Click" button in the attached file..

Please Login or Register  to view this content.

[arlu1201]

You need to ensure that the Monday, Tuesday in columns I & A match.

I didnt clear out any columns but did a copy paste. Ensure that there is no data in B3:F77

[swfarm]

APO

that works well with the exception that the first row runs next to the day so i cannot see the account number

[swfarm]

ARLU THATS BRILLIANT
THANK YOU
but does have a flaw see attached spread sheet after i have run your code

[apo]

oopss.. minor change..

Change this line to:

Please Login or Register  to view this content.

[swfarm]

apo

just for arguments sake if i had
15 days with monday what would happen ?

[swfarm]

if i was to allow 20 lines under each day
would i need to alter the code in anyway?
it would be easier to hide the empty rows after than trying to insert as im going
and 20 would be enough

[apo]

yeap.. you could have 20 row sunder each day.. no change in code needed..

If thats fine then you're good to go.. if not.. just need to code it to insert rows as it goes..

[swfarm]

I am more than happy to hide rows as i go
thank you both so much for giving up some of your time to help me
it really is appreciated

thank you

[arlu1201]

I cleared the data in column B:F and ran the macro after keeping 20 rows in between each day of the week and it worked fine.

[swfarm]

HI APO
SEEMS I SPOKE TO SOON
GETTING A RUNTIME ERROR 91
WITH THIS LINE HIGHLIGHTED cpyRng.Copy f.Offset(1, 1)
I HAVE ATTACHED SPREADSHEET AGAIN FOR YOU TO SEE MAYBE WHY THIS HAS HAPPENED

[apo]

Sorry.. thats me being to hasty (missus in background wanting to watch a movie...lol)..

Please Login or Register  to view this content.