Keeping data Closed

Question

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.


Configuration: iPhone / Safari Indeterminable

fdibbins · Answer

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

vcoolio · Answer

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.

Keeping data

2 responses

Similar discussions

Subscribe To Our Newsletter!