Keeping data
Closed
Mtujohn
-
Aug 24, 2016 at 05:11 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Aug 25, 2016 at 07:40 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Aug 25, 2016 at 07:40 AM
Related:
- Keeping data
- Tmobile data check - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to be offline in whatsapp when data is on - Guide
- Data transmission cable - Guide
- Spectrum data usage - Guide
2 responses
fdibbins
Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
1
Aug 24, 2016 at 08:33 PM
Aug 24, 2016 at 08:33 PM
Why would they delete data like that??/ It can be used for all sorts of tracking and stuff. Unfortunately, there is no formula that will do this for you, you would need some VBA code for that.
cant you talk them into just keeping all data, and adding to the end when new data becomes available? There are a shoe-box full of formulas that would then be available to run summaries and extracts
cant you talk them into just keeping all data, and adding to the end when new data becomes available? There are a shoe-box full of formulas that would then be available to run summaries and extracts
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Aug 25, 2016 at 07:40 AM
Aug 25, 2016 at 07:40 AM
Hello Mtujohn,
FDibbins makes a valid point, however if you still want to transfer all data to an archive sheet at month's end, then a code as follows may be all that you need:-
I've assumed that your data is from Columns A to M and that you have headings in both sheets. The code will transfer the whole data set to sheet2 on the click of a button and clear your main sheet of all previous data. I've attached a test work book at the following link to show you how it would work:-
https://www.dropbox.com/s/d0lqprv5mi7v6n6/Transfer%20complete%20data%20block.xlsm?dl=0
Click on the "RUN" button to see it work. Every time that you add new data to the main sheet then transfer it, it will be appended to the bottom of the previous data "block" in sheet2.
I hope that this helps.
Cheerio,
vcoolio.
FDibbins makes a valid point, however if you still want to transfer all data to an archive sheet at month's end, then a code as follows may be all that you need:-
Sub CopyData()
Dim lrow As Long
Application.ScreenUpdating = False
lrow = Range("A" & Rows.Count).End(xlUp).Row
If lrow > 1 Then
Sheet1.Range("A2:M" & lrow).Copy
Sheet2.Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues
Sheet1.Range("A2:M" & lrow).ClearContents
End If
Sheet1.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I've assumed that your data is from Columns A to M and that you have headings in both sheets. The code will transfer the whole data set to sheet2 on the click of a button and clear your main sheet of all previous data. I've attached a test work book at the following link to show you how it would work:-
https://www.dropbox.com/s/d0lqprv5mi7v6n6/Transfer%20complete%20data%20block.xlsm?dl=0
Click on the "RUN" button to see it work. Every time that you add new data to the main sheet then transfer it, it will be appended to the bottom of the previous data "block" in sheet2.
I hope that this helps.
Cheerio,
vcoolio.
