×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

A little help grabbing the last cells

A little help grabbing the last cells

A little help grabbing the last cells

(OP)
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.

RE: A little help grabbing the last cells

(OP)
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?  

 

RE: A little help grabbing the last cells

(OP)
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

RE: A little help grabbing the last cells

(OP)
*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.

RE: A little help grabbing the last cells

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/getfile.aspx?folder=429c5656-4cdf-4955-8c22-d618fac3edec&file=ExtractionDemo.xls

RE: A little help grabbing the last cells

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

RE: A little help grabbing the last cells

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources