Excel - taking a "snapshot" of data

  montyburns 07:55 15 May 2006

Any Excel experts able to help on this one?

I have a spreadsheet which monitors progress figures for staff (ie, how much of each activity they complete per week) It's dynamic, and so each time they update it, the old information is lost. Is there any way (macro etc) that I can set Excel up to, say once a week, copy the data set and append/paste it back in to another worksheet?

At the mo, the data is represented through formulas, so it would also need to "paste special" as values only

I've searched high and low online for an answer, but I'm currently stumped......

  dontmeshwithme 09:59 15 May 2006

The easiest way I can think of would be to use the macro recorder. Depending on the sensitivity of the data I would be prepared to have a look at the spreadsheet and offer advice on creating/editing a macro.

  VoG II 17:58 15 May 2006

dontmeshwithme is correct except that the macro recorder will record your exact keystrokes so it will for example go to the last row and column that you select. I presume that these (or either) will vary from week to week. Therefore you will need to modify the recorded macro to go to the last cell in the sheet that you wish to copy. The best thing to do is to record a macro to do this then copy and paste the code here and we can advise you what to change.

*Important* - we need to know the Column that will contain the last data if all rows are not populated in every column.

  Noldi 18:45 15 May 2006

Sorry to but in.
Can you not Select A1 then shift - CTRL+END to find the last cell holding Data when recording the Macro.


  montyburns 19:43 15 May 2006

I'll have another look at the layout tomorrow, in light of the advice, then post again!

Cheers folks!

  VoG II 06:18 16 May 2006

Yes you can use CTRL+END when recording the macro but the macro recorder will substitute the cell address - e.g. AB647 in the recorded code. We will need to substitute the VBA equivalent of CTRL+END - e.g to find the last used row in Column iCol

xRow = Cells(Cells.Rows.Count, iCol).End(xlUp).Row

  Jamb0 15:54 16 May 2006

Sorry to butt in with a different tack but the macro method seems quite complex.
Why not set one tab within the original spreadsheet as the data entry area and another tab for your calculation, analysis & reporting. Then you can copy all of the data area to a new spreadsheet saved as that week ending date. you can also copy your second tab to the new sheet to give you analysis of each saved week.
Alternatively, you could add a new tab to your original spreadsheet for each week and simply protect the previous weeks so that other staff cannot access them. you can also set a tab to do analysis of any one week by running vlookup et al and use replace to change the tab name whilst retaining your original full analysis.
It does sort of depend on what you need the data for as to what you would do with it!
It may be that by setting a new tab for data entry, that you could use the macro method above and not be concerned about the end of field if you set it to select all. However, you definitely need an analysis/reporting tab separate from the data input area.

  montyburns 07:52 17 May 2006

Can you give me a bit more time with this - brought home an old version of the spreadsheet last night by mistake.........


  montyburns 07:40 19 May 2006

Tried to send Vog a copy of the spreadsheet but not sure how to send it via the Forum.

Sent a message, but not even sure if that's working (as I just tried sending one to myself as a test but that didn't work.....)

Can anyone advise?

  montyburns 11:45 20 May 2006

Now sorted by VoG™ - for which thanks very much!

This is the macro:-

Sub WCopy()
Dim lastcol, icol, irow
Application.ScreenUpdating = False
lastcol = Sheets("Weekly").Cells(1, Columns.Count).End(xlToLeft).Column
icol = lastcol + 2
Sheets("Weekly").Cells(1, icol).Value = Date
For irow = 10 To 27
Sheets("Weekly").Cells(irow - 7, icol).Value = Sheets("Figures").Cells(irow, 6).Value
Next irow
Application.ScreenUpdating = True
End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Inside the iMac Pro - Apple's most powerful Mac yet

iMac Pro release date, UK price & specs

Comment nettoyer Windows et optimiser son PC gratuitement ?