Keeping data
Closed
Mtujohn
-
Aug 24, 2016 at 05:11 PM
vcoolio
vcoolio
- Posts
- 1352
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- July 19, 2022
Related:
- Keeping data
- Transfer data from one excel worksheet to another automatically - Guide
- Digital data transmission - Articles
- Download tiktok data after ban - Guide
- The data transfer rate ranges of wifi - Articles
- Vba code to search data in excel - Guide
2 replies
fdibbins
Aug 24, 2016 at 08:33 PM
- Posts
- 33
- Registration date
- Sunday June 19, 2016
- Status
- Contributor
- Last seen
- November 20, 2016
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
Aug 25, 2016 at 07:40 AM
- Posts
- 1352
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- July 19, 2022
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.