Trend analysis

I have had a little go at furthering this along a bit (Please see attached files) I have recorded a macro to extract the information and place it where I need it and another macro to save the first page to another file.

A couple of things I need help with are:

The first page is filled in once a week, run macro1 to extract the information to the correct worksheets run macro2 to save first page to a seperate file. How do I get the macro to run on the next line down each week instead of overwriting the previous week. And how do I get macro2 to write to sheet 2 then sheet 3, and so on, and not overwrite sheet 1 each week.

Thanks for reading this,

Best Regards,

Andy.

So, are you basically copying columns G:J of the first sheet into the other sheets, one row per sheet? Does the number of rows/other sheets vary from week to week or always 16? Your code can probably be shortened, just want to make sure I understand what you are doing. Also, what's the difference between the two attachments?

Hi,

thanks for taking an interest, Will always be 16 rows unless we add more equipment which I can't see happening for some time.

The difference between the two files is the trend analysis fist sheet is filled in each time, overwriting from the week before the other file is a copy of the newly filled out form kept seperately. Seems a bit messy but I thought this was the easiest way around it. The guy's who will be filling this in are not computer litterate so I just want one page for them to fill in. Not saying that I'm computer litterate !!!

Ah yes I got confused and though the two files were identical. Maybe something like this for transferring the data to other sheets?

Please Login or Register  to view this content.

That's a lot tidier and shorter thanks for that mate,

Best regards,

Andy.

Hi,

not solved yet.

I still need to be able to save the front sheet of the 'Weekly SAT Trend Analysis' file to the 'Weekly SAT Records 2011' file. The files are not identical. 'The Weekly SAT Records 2011' the tabs are dated Week Commensing and each sheet would be a copy of the front sheet of the 'Weekly SAT Trend Analysis' for that week. Is this confusing ?

Also, just a thought,

Once filled out (Weekly SAT Trend Analysis) front sheet, would it be possible to click on a button for the updates to take place.Either one button to update the lot or one buttun to update the WSTA file and one button to update the WSR2011 file.

I've tried and failed to add a button !!!

Best regards,

Andy.

Good work. Please could you post your code for the benefit of others?

Hi StephenR,

below is the code used, not awe inspiring as its only th top line different to the code you supplied me. I got it wrong when I was trying to add the 'Private Sub CommandButton1_Click()' as the top line to the Macro1. Got round this by pasting you code under the 'Private Sub CommandButton1_Click()' and deleting your top line,

Hopefully I have loaded the code correctly, apologies if I haven't !


Regards,

Andy.

Please Login or Register  to view this content.

Now all I need is to be able to save the first page of 'WSTA' to 'WSR2011' and this will be complete, each time front page is saved it needs to be saved on the next sheet dated with the W-C date,

Best regards,

Andy.

Hi,

I have now added graphs to each sheet in the 'Weekly SAT Trend Analysis' file. I still need to be able to save the front sheet as in the above message. Also how do you get the gaphs to update automatically. I don't want to have to select the 'source data' for a 12 month period and have a tiny graph that takes forever to be able to see what's going on. Also changing the 'source data' on each page weekly to incorperate that weeks results would also be a pain,

Nearly there, please help,

Regards,

Andy.

Hi,

just a quick one, is what I'm asking not possible, don't want to sound pushy but need to know if someone is able to help,

Best Regards,

Andy.

Below I have added a code that I have had loads of help producing and would like to share with the forum.

I have an excel workbook that is filled out once a week, the front sheet is filled out with the information required and I have a command button 'Update' that is clicked and th relevant information is taken from th front sheet and updated on the rest of the sheets in the workbook, this is then attached to graphs enabling to view trends.
I need to keep the front sheet as evidence and so I have a second command button 'Archive' this is clicked, select yes from the box, enter the file name (I use week commencing date) in the box and enter. the front sheet is now Archived in the same folder as the original workbook. On closeing the file select yes when asked if you wish to save changes. This is then ready to fill in next time. I can provide the workbook if somone once to have a look at it working.

A couple of questions

1. Can I strip the command buttons from the front sheet in the archive file, if so could someone help with a code.

2. Can the archived files be auto protected against anyone trying to make changes, again if so, could someone help and provide code to achieve this.

3. How can set the graphs so they update throughout the year, I don't really want to select the 'source data' area for a whole year because the info is so small untill you have been filling it out for a few months but also editing the 'source data' every week is a pain.

Thankyou for any help to achieve this so far,

Best regards,

Andy.

Please Login or Register  to view this content.