copy from another workbook's worksheet based on column name.

I am trying to search through a workbook that only will have 1 tab. I want to search for column names, as sometimes they will have less columns depending on how the reports are generated.

I want to copy that column, minus the header, into a table on a worksheet in a completely different workbook. I use the OpenFileDialog to select the report to copy from. The VBA code is on the workbook that is being pasted into, so it will be open.

Here is the code I have tried. I get a "Run-time error '13': Type Mistmatch" Error

Please Login or Register  to view this content.

Also tried this

Please Login or Register  to view this content.

this code is in a module on the workbook being copied into.

One error... wsImport is declared and used as a worksheet object, not a string. So you would use it just like any other sheet object.

Please Login or Register  to view this content.

Note, you can't copy an entire column from one sheet and paste into A2 of another sheet, only A1.

Originally Posted by JBeaucaire

One error... wsImport is declared and used as a worksheet object, not a string. So you would use it just like any other sheet object.

Please Login or Register  to view this content.

Note, you can't copy an entire column from one sheet and paste into A2 of another sheet, only A1.

Yeah I am gonna work on fixing leaving out the header.


When I try to copy it in the workbook the code is in, into another Worksheet on the same workbook even I still get an error.
It's not what I am really trying to accomplish, but not even this will work:

Error: "Run-Time error '1004': Method 'Select' of object'_Worksheet' failed"

Please Login or Register  to view this content.

It apparently won't even find the sheet in the same workbook as the code... nevermind another workbook.

Subscript out of range means an object you have named cannot be found. This usually means a sheet name is wrong or a workbook name is wrong.

The most common mistake is hidden spaces or outright typos. I see "sheet 4" in your code.... should that be "Sheet4" ?



I've explained and demonstrated a correction for your use of wsImport and wsExport objects. Keep reading the previous reply until you understand what I've shown, then try it.

Always click the DEBUG button and present the line of code highlighted as the error.

The RANGE() method requires you to use column "letters", the CELLS() method lets you use column numbers, optionally

Please Login or Register  to view this content.

The macro you pasted above is still wrong, the corrections I suggested are not there.

Yes, if column numbers are spotted into a Long Variable, best to go ahead and use it that way. The less plumbing the better.