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]

Depends, do you want to keep the cell references of any formulas that copy over or do you want it to increment? If the latter then this would work

    Columns("P:P").Copy Destination:=Columns("Q:Q")
    Columns("P:P").Copy
    Columns("P:P").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

but if the former then you would be better inserting a column in front of P then copying Q to P as values like so:

    Columns("P:P").Insert
    Columns("Q:Q").Copy
    Columns("P:P").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Now fortunately Excel is smart enough that you don't have to use a column letter, so once you decide the answer to the above we can just sub in X

 

[My Aswer Is This]

Did not exactly understand your post but try this:

Sub Copy_Column()
Columns(16).Copy
Columns(17).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Diogenes]

Thanks everyone for your comments.

The data in column P (16) is currently actual data. This data comes from an input sheet.

The data in column O (15) is historic and this is hard coded values.

The data in column Q (17) is forecasted data. This comes a forecast sheet.

As time moves on a particular column (P in this case) goes from forecast to actual to historic.

When it is actual and forecast the cells are formulas referencing other areas of the workbook but when historic it just needs to be hard-coded values.

So there is a column of "actual" formulas that is copied and pasted along the worksheet and it is replacing the forecast formulas in each column as it moves a long and leaving hard coded values behind it.

Does that make any sense?

Sorry for the confusion!

 

[My Aswer Is This]

If you want to paste values use: PasteSpecial xlPasteValues
If you want to paste formulas use: PasteSpecial

 

[Diogenes]

Just to shed some more light on my thinking the formula in the helper cell:

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

Always returns the column number for the actual data so it will go: 10, 11, 12, 13, etc.

Hence I was thinking of using the answer to this formula in some kind of R1C1 set up where the rows are irrelevant but telling the code to find column X based on the answer to the formula.

Then copy it, paste it in X+1 and then hard code what was in X.

It seems straightforward in my head but maybe it isn't?

 

[Diogenes]

Thanks,

Where I am tripping up is the R1C1 referencing based on the answer to a formula.

Is this even possible? i.e.

This formula

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

Tells me what C should be and I just want the VBA to go across C rows copy across to C+1 and then paste its own values back into C.

 

[Blade Hunter]

So in my code you would use columns(Range("A1").Value) instead of Columns("P:P")

Change Range("A1").Text to the cell where your helper column gives you the number

Columns("Q:Q") would obviously be changed to the same +1

 

[Diogenes]

I'm making a mess of this and this is what i currently have and I am getting a syntax error.

Sub Roll()'


    Active.Sheet
    Range("B10").Value = _
    Columns(Range("B10")).Copy Destination:=Columns(Range("B10" + 1))
    Columns(Range("B10")).Copy
    Columns("B10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End Sub

My reference formula is currently in cell B10.

Thanks for your help

 

[Blade Hunter]

Range("B10").Value gives you the result of your formula, it will be a number, use it anywhere you like as shown below.

Sub Roll()
    Columns(Range("B10").Value).Copy Destination:=Columns(Range("B10").Value + 1)
    Columns(Range("B10").Value).Copy
    Columns(Range("B10").Value).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub