Paste a range within a range

[nigelbloomy]

I am trying to paste part of the first row of cells in a range into a worksheet. The code below keeps giving me an error.
The ws is active, although I am not sure if it needs to be in this case. rngFull was defined earlier in the code as the set of cells a user had selected. stCol is a number. I want the first row, up to stCol (which would be several cells) to be pasted starting at A1 in the ws.

Please Login or Register  to view this content.

I was thinking that maybe Excel didn't like me pasting several cells that way so I also tried this code and got the same error.

Please Login or Register  to view this content.

I'm trying to avoid using the clipboard via copy and paste. I know this should be easy. What am I missing?

[stnkynts]

What is rngFull and stCol?

Depending on answer, maybe this might help

Please Login or Register  to view this content.

[nigelbloomy]

rngFull is a range of cells that usually has about 1,000 rows and 20 columns. stCol-1 is the last column from rngFull that I want in the header row on ws.

So if stCol is 5, then I want A1 to A4 from rngFull as the values to be pasted into ws.

I don't usually use resize, but using it this way still gives me an error.

Please Login or Register  to view this content.

[nigelbloomy]

It looks like my problem is in trying to select the first few cells of the first row in the range. I created rngHeader as a range to just pull out the section I need from rngFull. This also gives me an error.

Please Login or Register  to view this content.

This gives me a range with the values I want, but it doesn't allow me to use the variable stCol to define how many columns to include.

Please Login or Register  to view this content.

I can also do this and get what I want, but I don't always want the first several columns, sometimes I need the last columns.

Please Login or Register  to view this content.

Anyone know why I can use range with the quotes, but when I try to use cell() to get the range it errors out?

[stnkynts]

Don't know why it is not working for you; seems to function fine for me

Please Login or Register  to view this content.

Note:

So if stCol is 5, then I want A1 to A4 from rngFull as the values to be pasted into ws.

I think you mean A1 to D1.

[nigelbloomy]

You are right. I did mean A1 to D1. When looking at these lines of code, aren't you just calling ws1 again? I was trying to create a range so that I wouldn't have to have the code go back to ws1 over and over again. It looks like in the second line rngHeader is being set by the values in ws1 and not rngFull.

Please Login or Register  to view this content.

Your answer and debugging did give me an idea though that I think works.

Please Login or Register  to view this content.

I think part of the problem was that cells() was pulling the value from that cell instead of a reference to it. The .address seems to fix that and ends up giving me the same as if I had just used Range("A1:D1").