Need "clean" way to grab range of data

dwrecipe

New Member
Joined
Oct 3, 2006
Messages
4
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

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

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

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

dwrecipe

New Member
Joined
Oct 3, 2006
Messages
4
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
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top