Previous Topic Next Topic

Keeping data

Closed
Mtujohn - Aug 24, 2016 at 05:11 PM
vcoolio
Posts
1352
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 19, 2022
 - Aug 25, 2016 at 07:40 AM
Hello there, I have what I consider an interesting issue and would certainly appreciate some options for resolving it as I am no Excel expert.

We have a sales sheet that's maintained on a monthly basis and the data is then deleted. The purpose of the spreadsheet is to track sales against our monthly targets.

I would like to extract that data as it's input ( or before deleted at the end of month) and keep it in a separate spreadsheet to track all sales, by line, over time.

Is this something Excel can accomplish and if so, what would be the suggested formula(s)?

Your thoughts and input are greatly appreciated and I certainly look forward to trying them. I'll be happy provide further information if I wasn't clear enough in my need.

Thanks, and all the best.


2 replies

Answer 1 / 2
fdibbins
Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
 1
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
0
Answer 2 / 2
vcoolio
Posts
1352
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 19, 2022
 250
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:- 

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.
0