Copy data from 2 sheets based on date

Hello all. I have the following code that performs a row copy based on selected dates which is then pasted to another sheet as a report. I need to also perform the same copy from another sheet with the same structure so the All_Report contains the data from both sheets. The name of the other sheet to copy the information from is "Closed_Requests".

Please Login or Register  to view this content.

I tried

Please Login or Register  to view this content.

This gives me a compile error with the second 'IsDate' hilighted and stating "Expected: Then or GoTo"

Any help would be appreciated!!

Thanks,
Andrew

There is a syntax error in the second If staement. It looks like you are trying to qualify the ".Cells" references with worksheets("Closed_Requests"). If that is the case, the each occurrence of ".Cells" needs to be changes to worksheets("Closed_Requests").Cells.

While that may get passed you compile error, it may not do what you say you need. RowsWithNumbers will only reference the original sheet.

Looks like you have some confusion about the use of the "With" keyword in VBA. Somewhere above these lines of code is a "With" statement that is being used to qualify the ">cell" references. then, below this block is an "End With". The variable "Source" has nothing to do with this syntax. Check the Help documentation on "With" to get further information.

You will have to change your logic up a bit to make this work. Assuming that you want to add the closed requests to the end of the report, take the following steps:
1. remove the code that you have added from its current place.
2. after the for loop on x add the logic to copy the rows from the closed requests to the report. This involves 1) testing if you are running all_report, 2) setting source to the closed_requests, 3) determining which row on the all_report sheet to past to, 3) looping through all of the rows in closed_request, 4) do the matching and building up the intersection, 5) at the end of the loop, paste the selected rows in to the report, and then protect the source worksheet.

I can't directly give you the code because your workbook depends on a database that I do not have.

I have worked with this a bit but still not able to get both the Distribution sheet and the Closed_Requests sheet to combine on the All_Reports sheet. The attached workbook calls an Access database that you can ignore. All the data necessary for the workbook to function is hard-coded in the sheets. The SelectReport macro is the one I am having problems with.

The code creates a TempSheet which is supposed to receive the rows from the Distribution sheet and the Closed_Requests sheet and is deleted after the processing is complete. I stepped through the code and saved the TempSheet as TempSheet1. Not sure what is happening to cause the rows from the Distribution sheet to start at row A32 of the TempSheet, but they should actually start on row A1. Not sure if this is the reason rows from the Closed_Requests sheet don't get copied but help correcting this would be great.

Can someone please assist in correcting the code so the rows from both the Distribution sheet and the Closed_Requests sheet combine on the All_Reports sheet.

Thanks,
Andrew

I fixed a few bugs. See if this works better for you:
1. Set TEMPrngPaste = TEMPwks.Range("A3" & (LR + 1)) should have been Set TEMPrngPaste = TEMPwks.Range("A" & (LR + 1)). This puts the paste row at row 2 rather than at row 32
2. added line to protect the Closed_Requests sheet after All_Report processing
3. added a line to set RowsWithNumbers to Nothing during the All_Report processing. This was the basic problem that was preventing the macro from running. RowsWithNumbers contained the range from the Distribution sheet and the macro was trying to form a union with cells from the Closed_Requests sheet.
Test Distribution List.xls

Yes, I noticed that, too, but was not exactly sure what you wanted to do with it. I'm glad you figured it out. Happy VBAing.