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?

Sub x()

Dim i As Long, r As Range

Application.ScreenUpdating = False

With Sheets(1)
    i = 1
    For Each r In .Range("G10", .Range("G10").End(xlDown))
        i = i + 1
        r.Resize(, 4).Copy
        Sheets(i).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats
    Next r
End With

Application.ScreenUpdating = True

End Sub

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.

Private Sub CommandButton1_Click()
Dim i As Long, r As Range

Application.ScreenUpdating = False

With Sheets(1)
    i = 1
    For Each r In .Range("G10", .Range("G10").End(xlDown))
        i = i + 1
        r.Resize(, 4).Copy
        Sheets(i).Cells(Rows.Count, 2).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats
    Next r
End With

Application.ScreenUpdating = True

End Sub

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.

Private Sub CommandButton1_Click()

Dim i As Long, r As Range

Application.ScreenUpdating = False

With Sheets(1)
i = 1
For Each r In .Range("G10", .Range("G10").End(xlDown))
i = i + 1
r.Resize(, 4).Copy
Sheets(i).Cells(Rows.Count, 2).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats
Next r
End With

Application.ScreenUpdating = True

End Sub

Private Sub CommandButton2_Click()

Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub

With Application
.ScreenUpdating = False

' Copy specific sheets
' *SET THE SHEET NAMES TO COPY BELOW*
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas
On Error GoTo ErrCatcher
Sheets(Array("SAT Data")).Copy
On Error GoTo 0

' Paste sheets as values
' Remove External Links, Hperlinks and hard-code formulas
' Make sure A1 is selected on all sheets
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
ws.Activate
ws.Cells(1, 1).Select
Next ws



' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm

' Input box to name new file
NewName = InputBox("Weekly SAT Records 2011", "New Copy")

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "/" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False

.ScreenUpdating = True
End With
Exit Sub

ErrCatcher:
MsgBox "Specified sheets do not exist within this workbook"
End Sub