A little help grabbing the last cells

[jackosieves]

Hi guys. I am not the best at writing code. I typically just record simple macros. However, I have a sheet that gets updated daily and I want to be able to take the last 7 rows (a weeks worth) of data and export into a separate table on another sheet. Any help would be greatly appreciated. Thanks.

 

[MechEng2005]

See the file I attached. Is type of strategy something you think would work for what you want to do?

--MechEng2005

 

[jackosieves]

That's kind of essentially what I'm trying to do. The thing that is making it tough is the fact that the raw data is getting updated every day and I'm just trying to get it to where it only pulls the last 7 days at any given time. Something like, "Range("A65536").End(xlup).Select". Can I do .End(xlup)-1, -2, -3 etc?

 

[IRstuff]

ActiveCell.SpecialCells(xlLastCell).Select

gets you to the last cell


TTFN

FAQ731-376

 

[jackosieves]

Alright, I think I'm getting close here. I can't get my offset squared away. I actually want to take the last 8 rows, with data, in the spreadsheet. The dates go on for years.


Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlUp).Select
-->ActiveCell.Offset(-8, 0).Range("A1:L9").Select
ActiveCell.Activate
Selection.Copy
Sheets("All").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[jackosieves]

*note, the arrow I put in is to show the line of code that Excel is not liking. It is not in my VB code.

 

[Denial]

I think you can achieve what you seek without resorting to any macros, by using a combination of:
» A "dynamic range";
» The INDEX function;
» Entering this INDEX function as an array formula.

See the example that (I hope) I have uploaded.

http://files.engineering.com/getfil...955-8c22-d618fac3edec&file=ExtractionDemo.xls

 

[MechEng2005]

Oh, if you look again at the file I posted, notice on the summary tab that the dates automatically update. The dates use the " =TODAY() " function so the summary sheet always lists the last 7 days.

-- MechEng2005

 

[jackosieves]

Thanks for all of the help. I ended up getting it to do what I wanted by using the sumif() function. I set it up to where the data automatically gets pulled depending on what the date is. I'll continue to tweak it though and may try to incorporate what guys suggested in this sheet or others that I build.