Pasting copied columns in VBA

[Mr2017]

Hi

I've written some VBA code which selects then deletes certain columns.

I then want to cut two columns and paste them at the end of the data.

I've written the code below, but Excel doesn't like the last line, where I've selected column U and attempted to paste the columns I'd just cut (columns H and I).

Any ideas why?

TIA


Sub ColumnChanges


Range("C:D,F:J,AB:AB").Select
Selection.Delete
Range("H:I").Cut
Range("U:U").PasteSpecial.Values


End Sub

 

[rlv01]

In what way does excel not like the last line, i.e. what is the error message?

 

[MARK858]

It won't like it because you are cutting 2 columns and trying to paste to one.

Try changing

Range("U:U").PasteSpecial.Values

to

Range("U1").PasteSpecial xlValues

 

[rlv01]

Alternatively, use copy instead of cut.

Sub ColumnChanges()
    Range("C:D,F:J,AB:AB").Select
    Selection.Delete
    Range("H:I").Copy
    Range("U:U").PasteSpecial (xlPasteValues)
    Columns("H:I").Clear
End Sub

 

[MARK858]

I think that you will find that you still need to use either a single cell or 2 columns (by the way I don't think my last code will work but can't test on my phone)...

Sub ColumnChanges()
    Range("C:D,F:J,AB:AB").Select
    Selection.Delete
    Range("H:I").Copy
    Range("[COLOR="#FF8C00"]U1[/COLOR]").PasteSpecial (xlPasteValues)
    Columns("H:I").Clear
End Sub

 

[Mr2017]

Hi rlv01

Thanks for the prompt response - the error message is "Run-time error '1004': Unable to get the PasteSpecial property of the Range Class.

 

[Mr2017]

Hi Mark, thanks for the prompt response.

I tried that (please see below), but it still gave me an error message saying "Run-time error '1004': Unable to get the PasteSpecial property of the Range Class." It's a good idea, though. It just seems to be a strange problem! Any other ideas, please?

Sub ColumnChanges()


Range("C:D,F:J,AB:AB").Select
Selection.Delete
Range("H:I").Cut
Range("U1").PasteSpecial.xlValues




End Sub

 

[Mr2017]

Ok, thanks rlv01 - this code worked! Thank you! Can you please remind me how to paste the columns as they are, rather than pasting as "values" only?

 

[Mr2017]

Thanks Mark - this worked! But can you please remind me how to paste normally rather than specially in VBA? When I typed "Paste..." after selecting cell U1 in my original code, the default option was to "paste special" but I want to actually just paste the columns that have been cut.

 

[rlv01]

Can you please remind me how to paste the columns as they are, rather than pasting as "values" only?

Sub ColumnChanges()
    Range("C:D,F:J,AB:AB").Select
    Selection.Delete
    Range("H:I").Copy
    Range("U:U").PasteSpecial (xlPasteAll)
    Columns("H:I").Clear
End Sub