Date Prompt for Worksheet range

I have a workbook and would to insert a worksheet that will capture and add 
data in a separate worksheet based upon the prompt for date range that is 
entered by the user.

My question is two fold:

1.  How can I create a prompt for the user to enter a date range?
2.  How can I create a macro that will capture the data from another 
worksheet and create a new worksheet based upon the date range?

Any help would be appreciated.

Happy Holidays, 


0
msw (9)
12/20/2005 12:50:28 AM
excel 39879 articles. 2 followers. Follow

3 Replies
728 Views

Similar Articles

[PageSpeed] 20

In answer to question 1 this method from Ron de Bruin allows the user to 
select the date from a calendar. http://www.rondebruin.nl/calendar.htm

For question 2 you would probably need to give a lot more information 
before you get any good responses.

Hope this helps
Rowan

msw wrote:
> I have a workbook and would to insert a worksheet that will capture and add 
> data in a separate worksheet based upon the prompt for date range that is 
> entered by the user.
> 
> My question is two fold:
> 
> 1.  How can I create a prompt for the user to enter a date range?
> 2.  How can I create a macro that will capture the data from another 
> worksheet and create a new worksheet based upon the date range?
> 
> Any help would be appreciated.
> 
> Happy Holidays, 
> 
> 
0
12/20/2005 2:34:45 AM
msw

You could use a Data Filter to get the data between two dates.

You can move this filtered data to a new worksheet.

See Debra Dalgleish's site for instructions on these steps.

http://www.contextures.on.ca/xladvfilter01.html

As far as the macro goes, record what you do and see what you get for code.

You will probably have to edit any code a bit.

Also look at Ron de Bruin's site for macros for copying between sheets.

http://www.rondebruin.nl/copy1.htm


Gord Dibben Excel MVP


On Mon, 19 Dec 2005 19:50:28 -0500, "msw" <msw@hotmail.com> wrote:

>I have a workbook and would to insert a worksheet that will capture and add 
>data in a separate worksheet based upon the prompt for date range that is 
>entered by the user.
>
>My question is two fold:
>
>1.  How can I create a prompt for the user to enter a date range?
>2.  How can I create a macro that will capture the data from another 
>worksheet and create a new worksheet based upon the date range?
>
>Any help would be appreciated.
>
>Happy Holidays, 
>
0
Gord
12/20/2005 3:05:19 AM
Try EasyFilter
http://www.rondebruin.nl/easyfilter.htm

It have a option to create a new workbook/worksheet with the data for you


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Gord Dibben" <gorddibbATshawDOTca> wrote in message news:10teq1hhkpclonflj9nucrc5pj1gdcl5d7@4ax.com...
> msw
>
> You could use a Data Filter to get the data between two dates.
>
> You can move this filtered data to a new worksheet.
>
> See Debra Dalgleish's site for instructions on these steps.
>
> http://www.contextures.on.ca/xladvfilter01.html
>
> As far as the macro goes, record what you do and see what you get for code.
>
> You will probably have to edit any code a bit.
>
> Also look at Ron de Bruin's site for macros for copying between sheets.
>
> http://www.rondebruin.nl/copy1.htm
>
>
> Gord Dibben Excel MVP
>
>
> On Mon, 19 Dec 2005 19:50:28 -0500, "msw" <msw@hotmail.com> wrote:
>
>>I have a workbook and would to insert a worksheet that will capture and add
>>data in a separate worksheet based upon the prompt for date range that is
>>entered by the user.
>>
>>My question is two fold:
>>
>>1.  How can I create a prompt for the user to enter a date range?
>>2.  How can I create a macro that will capture the data from another
>>worksheet and create a new worksheet based upon the date range?
>>
>>Any help would be appreciated.
>>
>>Happy Holidays,
>> 


0
rondebruin (3790)
12/20/2005 4:09:20 PM
Reply:

Similar Artilces:

Excel number / date conversion
I've just pulled some data out of an ODBC connection into Access an then exported it to Excel. The dates are currently in the format 20031109. But the built in dat convertor (from Format Cells) doesn't do anything, even if I convert i to a 'proper' number first. How can I convert this to 09/11/2003 so that I can do some date - base arithmetic and work out difference in days -- markperr ----------------------------------------------------------------------- markperry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2759 View this thread: http://w...

Dates for European Summer Time
European Summer Time starts this Sunday (the last Sunday in March). Does anyone know of any formulae to compute the start and finish dates automatically for the current year please? TIA V http://www.greenwichmeantime.com/time-zone/rules/eu.htm Victor Delta wrote: > European Summer Time starts this Sunday (the last Sunday in March). > > Does anyone know of any formulae to compute the start and finish dates > automatically for the current year please? > > TIA > > V "Bob I" <birelan@yahoo.com> wrote in message news:u...

A reducing number by given dates
Hi all, basically what im trying to create is a spreadsheet showing ho many payments i have left on an item i purchased. Now i pay $100 o this item on every 2nd friday. So my question is, what is a formul that will look at todays date and reconise that it is the second frida when payment is due and reduce a given figure in a cell by $100. Fo example, Total cost is $1000, payments = $100, payments are due o 07/10/05, 21/10/05, 04/11/05 etc So when the 07/10/05 arrives tota cost will = $900, when 21/10/05 arrives total cost will = 800 etc Obviously when the date is other than specified the tota...

Julian dates
Is there a way in Excel to convert Julian dates? I have Excel 2000. Thanks in advance. Steve For real julian dates (see http://aa.usno.navy.mil/data/docs/JulianDate.html ) just subtract 2415018.5 for the 1900 date system or 2416480.5 for the 1904 date system (adjust for your time zone). For "julian" dates of the form YYddd, check out http://www.cpearson.com/excel/jdates.htm In article <07ea01c38853$bf22d520$a401280a@phx.gbl>, "Steve Cornelius" <sscornelius@comcast.net> wrote: > Is there a way in Excel to convert Julian dates? I have > E...

trigger to update last sold date in HQ item table
hi , i need to update automaticaly the last sold date in HQ from last sold date in store (since it is not updated from stores). can we use item dynamic table to update item table ? if so can any one give a trigger to do that. thanks I have no idea about HQ However, in my opinion creating trigger can slow down database executions. Be careful with that May be it would be better if you create a stored procedure to contain the update logic and then schedule the stored procedure to run hourly or daily ? rgds, Joie "Sad" <Sad@discussions.microsoft.com> wrote in message ne...

Declaring Range
Hi All, Is there any option to declare a range publicly. I have more than 10 to 15 modules I need to set the Rng as Range i.e., set Rng = sheets("Sheet1").Range("A:A") set Rng1 = Sheets("sheet1").Range("K:K") .....So on.. I have to do, in each module i need to set the Rng or Rng1 or Rng2 etc.... Can i public declare or set the Ranges. so that I can use in any module. Please let me know, how can i achieve this. Thanks in advance. Yes. I like to use this technique. I'll dedicate a General module that contain...

Command Button to import worksheets
On a worksheet I have the following data A 1 Name 1 2 Name 2 3 Name 3 etc up to 15 Within the same parent folder I have 15 single page workbooks named the same as col a ie Name 1, Name 2 etc I need to import copies of the single sheet workbooks as worksheets in my main file in the order they appear in COL A ie sheet1 then Name1, Name2, Name3 etc So far i have managed to do this with 15 command buttons with the following code; Private Sub CommandButton2_Click() Sheets("Front Sheet").Select PathName = Range("JA26").Val...

Adding cells from multiple Worksheets
Good day, I am looking for a way to add cells together from multiple worksheets within a single spreadsheet. The catch is: I want to be able to create the formula to allow me to insert additional worksheets and still come up with the correct total. Is there a way to reference a cell in a non-existent worksheet? Or is there another way to accomplish my goal? TIA - Joe Hi Joe Add two dummy sheets(empty sheets)with the name start as the first sheet and one with the name end as the last sheet of your workbook. this are empty sheets!!! All worksheets between these sheets will be Sum ...

Duplicate Worksheets in Multiple Excel Files
People: Say that you have several different Excel FILES and you want to check for duplicate WORKSHEETS in some of the files. Are there programs which can find the duplicate worksheets and eliminate them? Anne ...

Find Matching Records in Two Worksheets #2
I think my follow-up to my original question got buried in the sands of time ; ) so I am re-posting as a new question. I think the solution given below should work, but I can't quite get it to go. I need the help of an expert to "translate" the formula to match my spreadsheet. Please see below for the original thread: ------------------------------------------- I think this is the right solution, but I am having trouble getting it to work. When I remove all the extraneous columns and match my spreadsheets to the example (ServiceDate is column B, Procedure is column C, a...

Count if date is before value?
I have a roster of employees and there is a column for the date of thei last annual training. I am trying to have a total on the bottom of th column that counts how many people are delinquent (havent had the clas in over a year) in their training. Looks like this: Name Class Date John Sexual Harassment 4/15/2003 Harry Sexual Harrasment 3/17/2003 Don Sexual Harrasment 4/1/2004 TOTA REQQUIRED 2 2 would be the total if the date was set to 4/15/2004 I have tried COUNTIF(C2:C107">4/15/2004")...

Named Ranges in VBA
I need to know how to name a range using VBA. The example as follows does not work. Selection.Name = "Named Range". What is the correct syntax for this? -- Bruce The syntax is correct. Then Name isn't valid (contains a space). This worked ok for me: Selection.Name = "Named_Range" Bruce wrote: > > I need to know how to name a range using VBA. The example as follows > does not work. > Selection.Name = "Named Range". > What is the correct syntax for this? > > -- > Bruce -- Dave Peterson ...

Selecting and moving chart(s) within a worksheet using keyboard ke
Hi all, I have created some charts in a worksheet. I would like to be able to do the following if possible: 1. I have selected a chart (by clicking on the chart and handles appeared around the perimeter of the chart). What I would like to do is to be able to select the chart using keyboard short-cut and to move the chart around the worksheet also using keyboard keys rather than the mouse. At the moment I can only move it (a single chart) using the mouse. However strangely enough, if I select two or more charts (Shift+ Lt.Click) I am able to then use the arrow keys to move the chart. ...

Measure on the date different
Hi, I had a huge file of aroiund 10 000 line. This 10 000 line only consist of 500 items. I wish to have an analyise a information. Example in the file it have items, order date, recept date and qty. Item A order 3 time on 1 Jan but recept 1 time on 1 Feb and 2 time on 15 Feb. Item A order 2 time on 1 Feb but recept i time on 1 Mar and 1 time on 15 Mar etc for Item B...... Ans Item A 2 time take 30 days and 3 time 45 days. The numbers of days can be range 25 to 30 act as 30 and 40 to 50 can act as 45. etc for Item B ....... ...

auto import date/time from .txt file
is it possible for excel to automatically import date/time info from a .txt file in the following format: "LASAIR110","04/05/19","15:47:41",2700.0,45.00,"V6.3",0,0,0 ,1,433,486,236,113,294,196,103,328,4.993,1.0000,0.000,0.000 ,0.000,0.000,0.000,"22de", "LASAIR110","04/05/19","16:33:15",2700.0,45.00,"V6.3",0,0,0 ,1,458,432,163,55,107,55,46,139,5.007,1.0000,0.000,0.000,0. 000,0.000,0.000,2299, thanks in advance Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "D. Reid" <...

Count formula within a named range.
Hi, How do I change the following formula =SUMIF($F$39:$F$79,"PW Shopfitters",$D$39:$D$79)/COUNTIF($F$39:$F$79,"PW Shopfitters") to count within a named range (PW Shopfitters). Any help would be great. Cheers, Phil Hi A range name cannot have spaces, maybe that's your problem. Name your range PW_Shopfitters. Then replace your cell references with PW_Shopfitters, eg =Sumif(PW_Shopfitters,"PW Shopfitters",............ -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "PW11111" wrote: > > Hi, > &g...

Converting dates in text format to date format.
I can do this manually using =Datevalue("02/14/2007"). But all my dates (several hundred of them) are in the text form (02/14/2007). I've tried writing a macro to add the quotes at either end of the text form date within the parens but I can't get the macro to add the quotes without copying the first date in the column. I should be able to figure this out but so far all my efforts have been futile. Any suggestions would be appreciated. Thanks and regards, PJF Try Data>Text to Columns>Next>Next>Column Data Format>Date>MDY>Finish. Assuming you don...

Trouble with Pivot Tables & Named Ranges
Can I use a dynamic named range setup in one file as a range in pivot tables in another file? When I have the data source file (with the named range) and the pivot report files open, the pivot reports update just fine. But the moment I close the source data, the pivot reports no longer can update and I get a message that the source can't be found. If I reopen the source, the pivot tables work fine again - but as soon as I close it up, the reports no longer work. The source file is very large and I don't want to open it every time I have to update all the reports tha...

Query to extract data on the basis of date
hi, I have created a query to extract the details of data on the basis of date in the table. The problem is the query is not giving the correct details. Can you pls let me know how to solve this problem? Thanks in Advance for the help!!!! Regards, Karthik.... Hi Karthik, I'm sure you know exactly what result you want, based on your data, but keep in mind that no one else has this benefit. You will need to explain in more detail what your problem is, if you have any hope of getting your question answered here. Is the date in your table stored in a Date/Time data type, or in a tex...

Counting words within a merged range of cells
Hi Everyone, I have a merged range of A15:J469, and I'm trying to find the number of times a specific words appears in this range, some which have spaces (e.g.: sun life, which is also entered in cell B9 - i.e. whatever is entered in B9 is the search string). It needs to search for sun life regardless of the case (since it'd be Sun life if it appears at the beginning of a sentence). Is there a way to count the multiple number of times a specific word appears in the merged range? I'd appreciate any help I can get. Thanks. On Thu, 13 May 2010 14:37:01 -0700, Pman ...

Prompt user to enter tax wages if necessary in Payroll Manual Chec
When users enter manual checks, the taxable wage amount associated with that tax is often forgotten. I know I can always enter another transaction and update the taxable wages, but I would like to see the system ask me if taxable wages should be entered....as a reminder. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and ...

If pd in GP is closed, trx date should default to next open mth
-- Stephanie L.-Dynamics GP Consultant ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=6bbdda13-e4a8-4811-be4d-64740571b35d&dg=microsoft.public.greatplains So say it&...

Date and Time Picker issue
I am currently using Excel 2007 on a windows XP pro OS. I am inserting a DTP control into workbook, the control works until I close the workbook and open it again and the control is replaced by a red X and the control no longer works. My system originally did not have this control, I downloaded the mscomct2.ocx and registered it to have access to the control. Any ideas what may be happening here? Thanks for any assistance in advance... George ...

::: Problem with "if between two dates" :::
Hello, I have a problem with "if between two dates". My problem is describe into the following Excel file http://cjoint.com/?hpqqXE0lBq Could anyone help me? Many thanks in advance. Jacques I looked at your sheet and I came up with the following formula: =IF($B$14<C19,"Too Late",IF(AND($B$14>C19,$B$14<C18-1),"Exit Now",IF($B $14>=C18,"Not Yet",FALSE))) I had a bit of trouble understanding your question but I think this is what you wanted. Hope that helped Mark Hi, I will try your solution. It looks very good, I think you understo...

Frequency of a range
Please, I have a list of numbers, eg. : ( I8:I507 ) I need to know the frequency of a range at this list of numbers. That range is at C1 and C2 Cells, eg.: ( C1 = 1 and C2 = 13 ), so ( 1,2,3,.....,13). I used =COUNTIF(I8:I507;C1) , but I need to reach the range, not only one value. Is there a way to to this ? Using >C1 and <C2 ? Thanks Ren´┐Ż You might want to check out Tools > data Analysis > Histogram...............you can build a BIN (list) of your 1 to 13 numbers and get a distribution........how many hits on each number............ Vaya con Dios, Chuck, CABG...