VBA Copy Column "X"

[Diogenes]

Hi there,

I am having a bit of a brain freeze and was hoping that someone may be able to help me.

I am trying to write a VBA script that finds column "X" copies it across to column "X+1" and then pastes column "X" as values.

The formula that I have in a helper cell for determining column X is:

=IF(P5='Global Inputs'!$H$16,COLUMN(),"")

It would be great if someone could shed some light on the best way to make Q and P in the example below variable:

Sub Roll()'
' Roll column
'
    Selection.Copy
    Columns("Q:Q").Select
    ActiveSheet.Paste
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial
End Sub

It seems as though it should be pretty straightforward to write a piece of code that says find column 16 copy it across to column 17 and then paste the original column 16 as values but for the life of me I can't work it out today!

Any help would be very much appreciated.


Thanks,

 

[Blade Hunter]

Try getting rid of the brackets around the Destination

If it still fails please put this in the immediate window (including the ?) and press enter

Range(Cells(1, 1).Address & ":" & Cells(Range("C5").Value, Range("B5").Value).Address).address

Copy the result here

Then this one:

Range(Cells(1, 2).Address & ":" & Cells(Range("C5").Value, Range("B5").Value).Address).address

Copy the result here.

 

[Diogenes]

I tried the solutions to the various Run-Time errors however an error of one sort or another kept occurring. In the end I decided it was easiest to go with the more simple whole column approach rather than the specific range. The column approach seemed pretty slick and stable.

This just meant that I needed to redesign my workbook/worksheets.

If I have enough time I will have another crack at the range. (I have to do this on another workbook next week so I will try it on that and let you know)

Thanks for all your help.

 

[Blade Hunter]

No probs, always happy to help