Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Codename NX console…
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 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.
Sorry to but in.
Can you not Select A1 then shift - CTRL+END to find the last cell holding Data when recording the Macro.
I'll have another look at the layout tomorrow, in light of the advice, then post again!
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
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.
Can you give me a bit more time with this - brought home an old version of the spreadsheet last night by mistake.........
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?
Now sorted by VoG™ - for which thanks very much!
This is the macro:-
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
Application.ScreenUpdating = True
This thread is now locked and can not be replied to.