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

ABennett757

New Member
Joined
Mar 25, 2021
Messages
8
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,899
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,424
Messages
5,746,597
Members
424,032
Latest member
pochie2741

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top