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

Hi

I've attached a file that will hopefully get you going. Open your userform1, then select the from date textbox. This will open a calendar, so select your date then press the commandbutton. Same for the to date. Then press submit, followed by close.

HTH

rylo

Originally Posted by davesexcel

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

Dave, Once again you have came up with a solution to what I request, not sure quite how I will try to disect this to be able to use in my "real life" instances though.

Because the Macro is "prebuilt" into an existing sheet, in this workbook, would I be able to go to a different workbook, and perform this same task? Let me restate that clearer... I have several huge files with lots of data recorded daily concerning differnent aspects of business each on seperate worksheets, some in different workbooks. But I want to compile one workbook, that has about 30 worksheets with different information but only showing the last week of data, so its not so HUGE... , without a bunch of copy paste copy paste, blah blah blah. As this is a task, that I have to complete every Monday. How can I use what you show to do this?

Still not sure if that is clear enough for interpretation...so lets say I want to have a workbook with pages that mirror "Last Week" that you have done...but I dont want to go through all 30 worksheets and input the same date over and over again...as I want a composition for the same dates consistant in the entire workbook. Is it possible to have a "Front Page" and input the date, and have the worksheets reference the dates shown on that page.
And...is it possible to pull the data from other workbooks that are on saved on my system, but are closed, into this "Weekly" workbook?

If so any thoughts, on how to do this.


I am open to anyones thoughts, concerns, ideas, suggetions, please feel free to comment.

Originally Posted by jaslake

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 can open the file but it will not let me use the calendar, I am having the same error that I did with rylos attachment earlier in this thread...

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

I don't know what else I can do to be able to view this, does anyone else have any ideas?

From my understanding of the calendar code, as I have used this in 3 seperate user forms is this:

1. frmCalendar is just for the layout or appearance
2. mGlobals show the dates as a string
3. clsCmdButton makes all the buttons on the calendar works

All 3 of those must go in a worksheet to make the calendar work (you do not need to modify those codes at all), the trick is to connect those three with the user form, and you do that by entering part of this code:

Private Sub txtDate_Enter()
    g_bForm = True
    frmCalendar.Show_Cal
   ActiveControl.Value = Format(g_sDate, "Medium Date")
End Sub

Sounds easy for someone more skilled then me, but my issue is that I am not used to connecting it to a sheet without a userform, but I kind of like not having to use the userform, for this particular layout.

I think I need to name the fields "Start Date" field and "Finish Date" field are in to show something like "txtStartDate" and "txtFinishDate" and then Link the above code to those fields.

I have been playing around with it, and so far, no good.

But I will keep trying and if I am successful, as always I will post my results, until then...any help is appreciated.

Originally Posted by jaslake

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

Very good read, wish I knew this before I installed Office 2010 in the 64 bit mode...Thanks for sharing.
I checked out the downloads available for the 64 bit, but if you want to use it and share, you have to buy several licenses for it after the free trial period, as this is something I need my co-workers to use, and at work we use Office 2003 32 bit, I am not sure that this would be the best route for me to do. As I don't think the 64 bit will be compatible with the 32 bit version I use at work.

So I guess I will keep plugging away at my previous thread...