VBA code to copy columns from active sheet to new workbook with autofilter

[ABennett757]

I'm looking for code to copy contents from multiple columns in an active worksheet to a specified location in a new workbook. The original file with the data to be copied may have different names so cannot reference the file name for the source workbook. Also, I want the user to name the new workbook with the copied contents via save as so simply want the new workbook to open, paste the contents and then the user will save an close.

Here's how I would like to copy data:

Source range --> destination file range
A7:A --> B2:B
B7:B --> C2:C
C7:C --> A2:A

All source information information is within a table with header in row 6 (data starts in row 7).

I also need to specify the text that will be in cells A1:C1 in the destination file that will function as headers for the copied data. I would like to hard code that text.

Finally, the I only want data copied from the source file if the associated value in a neighboring column meets a certain criteria. My application is that I a trying to create an order form to order parts. The source file is the complete bill of materials for a project. I have a status column (D7:D) for "order status" and I only want to copy items where the order status = "Ready". If the Order Status is ready then copy the Vendor, Part # and price columns (range A7:C) to the new workbook. Once the data is copied from the source workbook, I would like to automatically replace the "Ready" status text with "Released" and place the current date in column E of all items copied.

Thanks for any assistance!

 

[Zot]

I've seen many people view including me ?

However, I think you will get faster reply if you can create sample of the sheet so that it is easy for helpers out there to start working on solution. Install Add-In XL2BB (the icon on the right most) and use it to copy paste the sheet if possible.