Need "clean" way to grab range of data

[dwrecipe]

Hello,

Right now I am paste linking data from one sheet to another, but am running into an issue.

The data sheet that I am copying information from "grows". Everyday it gets one cell longer.

Date (Column A) Hours Worked (columns B and C)
10-05-06 37 employee hours
10-06-06 39 employee hours

The information that I would like to track on the sheet that I am putting the information on, is a running total of the last 30 work days.

So the real question - how can I get excel to copy the most recent 30 days, since those days are always moving down on the data sheet. I do not have the ability to change how the data sheet is formated, so I need excel to recognize the last 30 cells that contain data. Then I can use the formula of selecting those on my tracking spreadsheet.

Any help would be much appreciated!

Thanks
DW

 

[larrydunn]

Here's one way. Just run the code, then proceed to the new sheet and paste.

Sub Get30Days()
Get last 30 days of input in columns A-C
Dim sngLastRow As Single
Dim sFirstCell As String
Dim sLastCell As String

'Move to Excel's end-of-spreadsheet marker
ActiveCell.SpecialCells(xlLastCell).Select
sngLastRow = ActiveCell.Row

sFirstCell = "A" & (sngLastRow - 30 + 1)
sLastCell = "C" & sngLastRow
ActiveSheet.Range(sFirstCell, sLastCell).Select
Selection.Copy

End Sub

 

[Andrew Fergus]

Hi

The following example shows 2 ways of doing this without using VBA. If you want to see all 30 days then the first 25-26 rows show you how to do this (I had to strip out a few rows to get it into a format I could upload). If you just want a formula that totals the last 30 days, then that is shown at the bottom of the sample.

If there are performance issues then a VBA solution would be better.

HTH, Andrew

NHL.xls
	A	B	C	D
1	RollingDay	Date	Hours
2	1	8September2006	37.5
3	2	9September2006	38.5
4	3	10September2006	38.0
5	4	11September2006	36.5
6	5	12September2006	37.0
7	6	13September2006	37.5
8	7	14September2006	38.5
9	8	15September2006	38.0
10	9	16September2006	36.5
11	10	17September2006	37.0
12	11	18September2006	37.5
13
14	18	25September2006	38.0
15	19	26September2006	36.5
16	20	27September2006	37.0
17	21	28September2006	37.5
18	22	29September2006	38.5
19	23	30September2006	38.0
20	24	1October2006	36.5
21	25	2October2006	37.0
22	26	3October2006	37.5
23	27	4October2006	38.5
24	28	5October2006	38.0
25	29	6October2006	36.5
26	30	7October2006	37.0
27		TOTAL	1125.0
28
29	ORSINGLEFORMULA		1125
Sheet2

The html maker mucks formulas with ampersands, so the two formulas look like this :

The date formula:

=INDIRECT("Sheet1!A" & A2 + 8 + MATCH(MAX(Sheet1!$A$39:$A$1000), Sheet1!$A$39:$A$1000))

The total formula:

=SUMIF(Sheet1!A39:A1000, ">="&(MAX(Sheet1!A39:A1000)-29), Sheet1!B39:B1000)

 

[dwrecipe]

Thank you both for your help. This will greatly improve the effficiency of my project. I may have a follow up question in a few days regarding another implementation of that formula, but this is exactly what I was looking for right now.

Thanks again,

DW