Copy several columns into a specific range (new workbook)

Hi there,

I have been lately working with VBA and I got stuck, selecting the data I am interested in and copy it into a new workbook. I have seen examples and youtube videos but I have clearly missed something.

I have posted part of the code, as the rest is working fine:

Please Login or Register  to view this content.

Basically I want to copy several (but not all) columns from the opened file, into specific columns inside the new workbook.

Any help is highly appreciated

Many Thanks

Ricardo

I eliminated some duplicate declarations (and duplicate code), moved your declarations up to the top, and re-wrote the portion of the code that copies columns to use .Resize and (...).Cells.Value = r1.Cells.Value methods instead of the .copy method. I did not add any variables and I tried to leave most of your code b/f the copy attempt was made.

If at all possible, avoid the .copy method unless you want to copy over formatting and don't want to spend time to write additional code to carry over the formatting. It's orders of magnitude slower.

Please Login or Register  to view this content.

...it compiles -- but I had to add a "Next NFile" because it looks like this is only an excerpt from your code.

Hope this helps.

Hi Ricardo and welcome to the forum,

I can do your problem without needing any VBA in about 10 mouse clicks. Here is how......

Excel realized a few version ago that users wanted to import all kinds of data into Excel. They created a tool they call now Power Query. I have it built into my 2016 version of Excel and if you don't have it you can download it for your version.

Power query is like a pre-processor of data from other file types. Here is how I'd do you problem:

1. Open a new and blank Excel Workbook
2. In 2016 I click on the Data Tab and then the "New Query" dropdown.
3. Select From File -> From Workbook
4. This shows a dialog to pick the file I want to import the columns from. Select the file and click the Import button.
5. You will see a dialog asking which sheet(s) you want to import columns from. Select a sheet.
6. Now click on the EDIT button on the lower right of this "Navigator Dialog Screen"
7. Click on the Column Headings you want to import, until they are all selected.
8. Right click on any of the selected column heads and then on "Remove Other Columns"
9. Click on the "Close & Load" icon in this window.

DONE!!!! No VBA needed as I used Power Query to import columns from another workbook.

Read more about it at:
https://support.office.com/en-us/art...5-89F6269CD605