Copy range and paste to next blank column

[F1akman]

I have been trying to get VBA to copy a range and paste the values into the next blank column in a range.
I'm working with the following code:

Please Login or Register  to view this content.

This has been the closest I could find to what I needed, except this goes farther right than I need. I need the code to find the next available column in a range and paste there, instead of finding the cell after that last non-blank.

I'm sure I'm missing something simple.

Thanks.

[1aaaaaaaaaaaaaaa]

Hiya,

Could you provide a sample workbook that we can review? It would help us work through your use case requirements. Thank you!

[F1akman]

Poizhan,

Could not upload anything due to policy. But here is a subset of data. Columns G and H are titles and daily numbers calculated from another worksheet. I want to copy H2 through H6 and paste the values into the next available column between A and F.

A	B	C	D	E	F	G	H
10/2/2018	10/3/2018					DataSets	10/4/2018
4	6					Data1	11
3	4					Data2	6
11	15					Data3	17
8	8					Data4	10
6	4					Data5	4

I hope that helps. I think I'll start using CSV next time. Typing into the table is a pain lol

[1aaaaaaaaaaaaaaa]

Hi, this might work for you

Please Login or Register  to view this content.

[F1akman]

Hi, this might work for you

Please Login or Register  to view this content.

Sweet!! One alteration, can we make that past values only?

[1aaaaaaaaaaaaaaa]

@F1akman, not sure what you're asking for with "can we make that past values only" sorry could you explain further

[F1akman]

Sorry, this currently pastes the copied text and all formats as is. I would end up using this in other workbooks where the data being copied may be formulas. I only want to copy the result of the formulas, similar to Right click + v to paste "values".

[F1akman]

Bumping thread to see if there is a way to get this to paste over when the source fields are calculations and not static data. I can't seem to find a way for the xlPasteValues to work with this script.

John

[daboho]

Maybe like this

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

[Jim Clayton]

Pretty sure that the below works...not the cleanest thing ever created but, functional.

[F1akman]

Pretty sure that the below works...not the cleanest thing ever created but, functional.

Thank you for the script. This pasted below a bunch of other data further down the sheet. I need to paste a column of data to the next blank column in row 3, would that use columns.count? I'm trying to wrap my head around this.

[Jim Clayton]

Well that's not any good...think I sent you the wrong thing...had a few different notepad sheets open when I was messing with it. Let me see what I did here. Tks.

[F1akman]

Maybe like this

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

First script, I get Syntax Error on Dim rg Range.
Second script, I get Type mismatch on If Not LEN(r.Value) Then

I'm a bit confused on these as well. Where would I paste the copy? Sorry if I seem dense on this.

[daboho]

First script, I get Syntax Error on Dim rg Range.
Second script, I get Type mismatch on If Not LEN(r.Value) Then

I'm a bit confused on these as well. Where would I paste the copy? Sorry if I seem dense on this.

I am sorri change

Please Login or Register  to view this content.

[F1akman]

I am sorri change

Please Login or Register  to view this content.

Not working on that one. The selected area gets copied, but nothing pastes. Not sure I get the If/End If on this one.

Thanks