Excel: Can data be transferred automatically after a month?

Question

I am doing a spreadsheet of bids my company is doing for construction. Because we do about a page a day of bidding this report can get quite lengthy. Is there any way that the excel cells can be transferred automatically to another spreadsheet after about a month or so?

Answer 1

Here's a thought. Instead of putting it into a seperate document why not enter a seperate column for an expiry date (example: one month from the current date) and then filter the expired data out.

To do this, go to your top row and select Data, Filter, and AutoFilter.

Note: if you have more than one header row it can cause a slight problem but it is easily solved. Here's an example (note ignore the dots - this is the only way I could represent columns in Yahoo Answers):

..................................... Construction Bids .......................
Company ............................. Address ............................... Bid

In this case the title can confuse the autofilter. To get around this, insert a row between the title and the column headings so it looks like this:

..................................... Construction Bids .......................

Company ............................. Address ............................... Bid

You can adjust the height of the row so you hardly notice it. Now click on the column headings and select Data, Filter and AutoFilter. Notice the little arrows that show up beside each column. Go to the column that has your expiry date in it and click on the little arrow. Go down to the bottom of the list and select custom. If you want only the bids that expire today or in the future then select "Is greater than or equal to" from the drop down list and then type in today's date (8/15/07). There are lots of choices to choose from in the drop down list so you can customize it the way you want.

This filters the data but doesn't delete or move it. To view all the data again, simply select Data, Filter, Show All.

Hope this is helpful. If you need more help with this, drop me a line using my Yahoo Answers e-mail.

Answer 2

Yeah, it could be done. You would have to write a macro in Excel. You would need to go into Visual Basic Editor in Excel (Alt + F11), then in the Project Window you would want to open up the Microsoft Excel Object "ThisWorkbook" by double-clicking on it. That is where you want to put your code to copy the cells from one sheet to another.

For your Object dropdown list select Workbook.

For your Procedure dropdown list select Open.

Then add code to Add a worksheet and copy the cells to that worksheet. By entering the code in that window, it will add the sheet and copy the cells when someone opens the workbook. You'll need to make a reference to the current date and entering a date that is entered on the worksheet to reference from.

Below is some VBA code that I wrote that will add a new worksheet when you open the workbook and it's a new month. Just make sure that you keep the most current Worksheet tab at the far left and don't mess with the Date that is set in the worksheet's header. It will even add the date and tab to a new blank workbook if needed. The only thing you'd need to work on is the code for copying the data from one spreadsheet to another. I can't really tell you how to do the copy without knowing the cell range you are wanting to copy from and the range you are wanting to copy to.

Private Sub Workbook_Open()

'Written By: Keith Sinders
'Date Written: 8/16/2007

Dim CurYear As Integer, CurMonth As Integer
Dim NewMonths As Variant, FirstDay As Date
Dim LastPeriod As Date
Dim LastMonth As Integer, LastYear As Integer

NewMonths = Array("Jan", "Feb", "Mar", "Apr", _
"May", "Jun", "Jul", "Aug", "Sep", "Oct", _
"Nov", "Dec")

CurYear = Year(Now)
CurMonth = Month(Now)
FirstDay = CurMonth & "/1/" & CurYear

If Sheets(1).PageSetup.RightHeader = "" Then
With Sheets(1)
.PageSetup.RightHeader = FirstDay
.Name = NewMonths(CurMonth) & " " & CurYear
End With
End If

LastPeriod = Sheets(1).PageSetup.RightHeader
LastYear = Year(LastPeriod)
LastMonth = Month(LastPeriod)

If CurYear - LastYear >= 1 Then
Sheets.Add
With ActiveSheet
.Move Before:=Sheets(1)
.Name = NewMonths(CurMonth) & " " & CurYear
.PageSetup.RightHeader = FirstDay
End With
ElseIf CurMonth - LastMonth >= 1 Then
Sheets.Add
With ActiveSheet
.Move Before:=Sheets(1)
.Name = NewMonths(CurMonth) & " " & CurYear
.PageSetup.RightHeader = FirstDay
End With
End If

End Sub


If you have any questions, feel free to write me.

PS - If you use my source code in any matter, make sure to give me credit for the work. Programmers tend to get upset if someone uses their work and tries to claim it as their own.

It also looks like it cut off the "r" on RightHeader in a few places in case you were wondering why the three dots are there.

Answer 3

You would still have to do this manually using a Macro.

It is probably easier just to right click on the Tab and then do a Move or Copy... and then Copy the contents to another sheet and start from there. This will create an exact copy of the sheet and data and then you can rename the tab to something else.

Answer 4

you ought to use 3-d formula which reference a similar relative cellular place in the time of dissimilar sheets. in the adventure that your columns and rows selection from sheet to sheet, i desire to advise making use of ASAPUtilities to merge information in the time of dissimilar sheets. ASAP Utilities is slightly freeware that automates the main elementary Excel initiatives. Merging in the time of sheets takes basically a 2nd, and could create a sparkling precis sheet which you have got the potential to artwork on.