I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

or even

Set rHeader = Range(pWorksheet.Cells(1, 1), pWorksheet.Cells(1,
iColCount))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"merjet" wrote in message
...
I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

That worked, thank you.
2 questions: (If need a separate thread please let me know.)
1) Why is Set used in this situation?
(Unless you know a good book or website to read, then I will take the time
to research instead of leaching.)
2) I tried subbing Me.Worksheet for pWorksheet and it does not work.
(Same as above)

Thank you

"merjet" wrote:

I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

You are using Set because it is an object variable you are dealing with.
With normal variable you load them with Let, but VBA defaults that so you
can get away without using it, but you must use Set with objects.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Tim" wrote in message
...
That worked, thank you.
2 questions: (If need a separate thread please let me know.)
1) Why is Set used in this situation?
(Unless you know a good book or website to read, then I will take the time
to research instead of leaching.)
2) I tried subbing Me.Worksheet for pWorksheet and it does not work.
(Same as above)

Thank you

"merjet" wrote:

I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

Thanks, that makes sense.

"Bob Phillips" wrote:

You are using Set because it is an object variable you are dealing with.
With normal variable you load them with Let, but VBA defaults that so you
can get away without using it, but you must use Set with objects.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Tim" wrote in message
...
That worked, thank you.
2 questions: (If need a separate thread please let me know.)
1) Why is Set used in this situation?
(Unless you know a good book or website to read, then I will take the time
to research instead of leaching.)
2) I tried subbing Me.Worksheet for pWorksheet and it does not work.
(Same as above)

Thank you

"merjet" wrote:

I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet