Copy rows to new sheet based on date range selected on a form

[drewship]

This question originated here and is not solved yet: http://www.excelforum.com/excel-prog...ml#post2088802

but since I have done some additional work, I have additional questions so thought I needed to make a new thread.

I hope someone can help me with the below issues and VBA is perfectly fine. This spreadsheet must perform calculations for every line since new items are added every day, so VBA is probably better than copying formulas down every line of the spreadsheet:

With the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.

Also, I need to keep a total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well.

I have a pivot table on Sheet1 where I am trying to get the totals and averages described above but I am not sure it can do what I need. In column 'B' I need the total number of each item as well as the total number of all items. I tried various formats and adding the totals from the Totals tab but I have not figured it out.

Thanks in advance,
Andrew

[Spreadsheet_Booster]

Hi! Andrew,

I suppose that iI might offer my help, however I get confused by your description of the problems as well as the process of the VBA code in you file. Do you have an IM tool such as Google Talk, so that I can know what exactly you want and maybe we can make it together~

if possible, contact liuchanghong2000$gmail.com (replace $ with @). I'll be glad if I can help you!

[drewship]

Thanks Spreadsheet_Booster, This is something for work so I do not have IM capability.

I have worked on it some more and hope someone can help with this code:

Please Login or Register  to view this content.

In the uploaded example, I want to press the button on the Utilities tab, select dates from May 7th to May 11th, name the sheet, and press Start.

When Start is pressed, I want to copy the first 2 rows over as the header to a new sheet (which was named in the form) and then copy every row that has a date in column "D" that is between or equal to the dated selected in the form. In this case, the Example tab contains the desired output. All new tabs should be placed at the end.

The actual output was the test2 tab. Can someone help me tweak this?

Thanks,
Andrew

[drewship]

No takers for the last bit of code??

Here is something I found on the forum that seems to be more complicated (to me anyway) but I have been able to tweak it to at least start copying data from my Distribution sheet to a new sheet, test5 in this case.

Please Login or Register  to view this content.

Some problems are that it is not copying the complete header, it included filters (don't need those), changes column 'L' on the Distribution tab (don't want to change anything on the Distribution tab), and is not copying the rows that meet the selected date range.

If anyone could help it would be greatly appreciated.

Thanks,
Andrew

[drewship]

Bump no response

[drewship]

Updated workbook attached. I have worked on this more and have cleaned up the code some. This is what I have:

Please Login or Register  to view this content.

This will copy the 2 header lines to a new sheet with the name you input on the userform when the button on the Utilities tab is pressed. The 2 problems I am still having is that column 'L' data is deleted and shaded grey after the processing is complete, and none of the data within the dates selected on the userform is copied to the new sheet which should look like the Example Output sheet. Can anyone help me with this???

Thanks,
Andrew

[drewship]

I was able to figure out what was deleting my data on the distribution tab by walking thru the code using F8. I am still unable to copy teh rows based on the userform dates. Here is the latest code:

Please Login or Register  to view this content.

I commented out

Please Login or Register  to view this content.

since this is what I am trying to adapt to match a date range. Does anyone know how to do this?

Thanks,
Andrew

[drewship]

All,
Please see link to other thread which has been 'solved' for the updated workbook.

Thanks to everyone who assisted!!!

Andrew