Macro To Pull Last week worth of Data out of a Worksheet

I have a sample work book with 2 worksheets, one is titled All Data", the other called "Last Week"

Is it possible to write a macro, to pull data between a "given" time frame? I would like to build a macro to say, ok if this date is between this day and this day pull this data into a seperate worksheet, with this formatting. Any ideas of How I would approach this, or other suggestions?

I was thinking maybe I could use a userform, to input the dates and to press submit, then have the macro create the sheet with the data that is called...but that is just a thought, not sure if that is possible or not.


Update, Ok I have built a userform, but right now there is no codes inputted. Not sure how I connect the cells to the calendar option yet either...but I have attached the amended file...


Ok I have built some code into it, but it still needs some work, check amended file, for updated information...

Check this out, done in XL'07
Click J1 to reset the ComboBoxes, select the start date and the end date then click the button

Originally Posted by rylo

I've attached a file that will hopefully get you going.

I have opened the file but...It states "Unable to find Object, Not available on this Machine", I can see inside the vb, but it will not let me see the calendar that you added, I am using a Mac, but running BootCamp with Windows 7 64bit, and Excel 2010...so I do not know what the problem would be... but I do appreciate you assistance

You may have to edit the msgbox, it is reversed.
If you have additional queries to this thread. you will have to add additional information or examples, do you want to loop through worksheets and filterr them so the results are on one sheet?

Ok, Here is a little example, that I set up...using a similiar format as Dave illustrated in the thread above.
I am not sure how I can attach the calendar option to assist in Date Selection (it is already imported into the attachement in the VB program.)

But if you look at the "Front Page" you will see what I want it to do. I want to be able to input a range of dates (again using the calendar option -all the code is in the vb already), and for it to automatically pull the data between those dates from the sheets labeled "Down Time Log (1st Shift)" and "Down Time Log (2nd Shift)" in an identical format and perfably sorted by Date/shift...so that I see both see Dec 1 first shift data, then Dec 1 second shift data, Dec 2 First Shift Data, Dec 2 Second Shift Data, in that order on the front page if the range is 12/1/ 2010 - 12/2/2010

See attachment
Also, I have installed a user form called, frmDateControl, as an alternative to the activex controls...

Hi 00Able

In the attached, I've added a new UserForm2 (I didn't want to have to wade through your code that creates the Calendar so I added my own). There is code behind UserForm2 that you can review.

I've also added Module1 that does all the grunt work for the Buttons on "FRONT PAGE"

There is no error checking; should you decide to use this, you'll need to add that.

Play with it and see if it does as you require.

Hi 00Able

I'm running this on a Mac with Fusion but am using XP and Excel 2000 and/or 2007. I'll take another look at it using your calendar. It may take a while...lots of code there.

John

Hi 00Able

Take a look at this link http://social.answers.microsoft.com/...9-e757223ebdde it may help explain what's going on. Don't know if or how it helps but it may give you something to research in your spare time.

John

Hi 00Able

Where did you get this calendar code? Perhaps if I looked at your source, I could understand how all this links together.

John

Hi 00Able

Well, I got your code to run on my machine. I need to do a lot more testing but before I go beating the horse, I need to know if it runs on your machine.

Again, error checking needs added.

I'd still like to know the source of this code.

Let me know what happens.

John

Hi 00Able

I've downloaded the file from http://www.4shared.com/file/10957606...endarv102.html. I can follow what it's doing.

Couple of questions;

First question
Does the code work in your environment?

BootCamp with Windows 7 64bit, and Excel 2010

It does work in my environment, Fusion, Windows XP, Excel 2000.

Second question
There's a LOT more code in your posted file. Why?

John

Sorry, I should have ask "Does the sample file in the download work in your environment?"

Hi 00Able

Alright then. We have code that works for both of us (at least in the sample file that we both downloaded). We have a baseline to work from. I think I can resolve the issue (don't know for certain but I love a challenge). I'll get back to you ASAP.

John

Hi 00Able

Where did you post the "New" attachment? If to a previous post, I'd have no way of knowing that. I ALWAYS post new attachments to a new post to let viewers know that they have updated information.

If you have a new file, please include it in a new post to your thread.

John

yeah its the only attachment in this thread, but again all it includes is a userform layout, nothing big, I will include it here as well... I included it as I was hoping to be able to connect it to the activex controls on the "Front Page", but I have no knowledge of how to assign it to those fields (only because I can't get those fields to do what I want it to do and I am more familiar with the use of userforms), alas many attempts, many failures...but I try try again...

Hi 00Able

I'd like you to humor me for a moment if you will. Open the attached file and click on Calendar then click on a date (any date). Does it fill the "Active Cell" with the date you selected?

This is just a test so I know we're functioning the same on our platforms. We're on two different operating systems (Windows 7 vs XP) with two different versions of Office (2010 64 bit vs 2000/2007 32 bit) with two different versions of emulation Software (Bootcamp vs Fusion).

I 'm pretty certain we can get this resolved but I'm going to be a pain in the butt. I'm going to ask you to test things I have working on my platform each step of the way.

Are you game?

John

Alright, good.

I'm posting a test workbook with this. It has error checking issues but I'd like for you to see if it runs in your environment. I'd like you to click on the box to the right of "Start Date", select a date then click on the box to the right of "End Date" and select a date. Then click "Pull Data".

Remember, there's no error checking yet so limit your choices to dates available in the files.

I'll be online for another hour or so, so let me know what happens.

John

Now this returns errors, as soon as I open file I get the first error, I labeled them according to where I see them...and try to walk through them...hope this helps, It does not show the calendar, nor let me enter any dates in the field.

I believe I am getting this error because of userform2 that is in the VB project, I dont think we are using that, so can we just delete that form out?

Hi 00Able

Fishing here. I've only changed about four lines of code in the test file I sent you previously.

Look at the file I've attached regarding References. Are your's set the same?

John

Yes, you can

I believe I am getting this error because of userform2 that is in the VB project, I dont think we are using that, so can we just delete that form out?

John

here is my screen shot

I'm going to clean up the file, removing all extraneous code, Userforms, etc.

I'll post this new file later this PM.

John

OK, get rid of the missing reference (unclick it) then click OK. This should eliminate at least one issue.

Going to bed soon. We're in the same time zone. This is only difficult because our environments are so different. The encouraging thing is that the code worked here

Yes it works fine, I was able to select a date and it appeared on the active cell...

We'll get it...if you wish.

John

Hi 00Able

I've removed all extraneous code, modules and forms from the attached. Please see if the results are any better.

John

Hi 00Able

This should be no problem

if we can make it erase all the previous data each time we reset the dates

said the blind man.

I'll work that into the code and get back to you. All else seems to work as expected?

John

Hi 00Able

Adding new dates "should" not be a problem (yeah, right!). There are error checking issues to deal with.

I'm not sitting in your chair and I can only guess at what you need. I'll try to anticipate those needs but, ultimately, only you (and your users) know.

I'll work on this for the moment

erase all the previous data each time we reset the dates

May well post tonight.

John

Hi 00Able

Try the attached to resolve this issue

erase all the previous data each time we reset the dates

Hope we've not screwed it up!

John

Hi 00Able

If I understand the issue, this is resolved in the attached

get the formatting to do the same

I've also added error checking in this version.

John

Hi 00Able

Glad to be of help. I'd assume your going to run it on a copy of your real file? Let me know of issues.

I've got an updated version that deals with locking of cells, protection, etc. I'll post it in a short while. If you're interested, fine. If not, fine.

John

Hi 00Able

I was having a problem with protection schemes in the earlier version so I excluded it. I was able to sort out my problem and that feature is included in the attached.

John

Hi 00Able

Yeah, well. I was just trying to tighten things up a bit. The code runs fine from the Forum copy and on my machine. Must be an Excel version issue. If that's what it is, I can't resolve it as I don't have your version.

Hopefully, Test Tracker 9.xls performs to your satisfaction. We'll keep playing with that version.

Let me know of issues.

John