Function call returns actual code instead of copied range

[rlg50]

I am baffled with this. I've never seen vba code pasted in the actual excel spreadsheet before. Here's the code I'm calling:

Please Login or Register  to view this content.

Here's how I'm calling it:

Please Login or Register  to view this content.

Here's what is returned and pasted in the spreadsheet:
Sheets("Analog").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Any suggestions on how to copy and paste the range instead of the actual code would be great.

[Leith Ross]

Hello rlg50,

Welcome to the Forum!

Here is the corrected and working code. Copy and Paste this code into a standard Module, and delete the old code.

Please Login or Register  to view this content.

[rlg50]

Is the only thing that you changed was the arguments for GetInputAndCopyData?

[Leith Ross]

Hello rlg50,

I made a few other changes to the Sub as well. Also, I changed the line below which is in GetInputAndCopyData...

Please Login or Register  to view this content.

[rlg50]

OK. I see that now. I ran the code and I'm getting the following error: Paste method of worksheet class failed using the following code to call the above corrected code.

Please Login or Register  to view this content.

[Leith Ross]

Hello rlg50,

That is because ""EMS Composite Name"" is not a valid Range name. To me it looks like a column header.

[rlg50]

Please Login or Register  to view this content.

I may need to clarify something. What I'm doing is, adding worksheets from another workbook into my workbook, say I import a worksheet named Sheet2. From "Sheet 2", I want to copy all data in column "EMS Composite Name" and paste it in "Analog". If I run the following, again, it pastes the actual code in my worksheet.

Please Login or Register  to view this content.

[Leith Ross]

Hello rlg50 ,

Is "EMS Composite Name" a named range, column header, what?

[rlg50]

It is a column header. So I pass the worksheet name and header name, then search for that column and then copy the data under it, return the copied data and paste it into another worksheet.

[rlg50]

It might help if I told you that the header is located on row 6 and there are about 15 other names on that row, so I need to select the correct column with "EMS Composite Name" in it.

[rlg50]

The jpg attached shows a quick illustration of what I'd like to do. Also, if it's easier to just go ahead and paste the copied data instead of returning it and pasting it, that will be fine too.