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,

 

[Diogenes]

Just about there.

That is copying the formula into the 1st column.

I've been playing around with Destination:=Columns(Range("B10").Value + 1)

Which seems to just result in errors and then the code doesn't run.

 

[Diogenes]

EDIT: I've just realised my error!

Thank you so much for your help!

 

[Blade Hunter]

Open the debug window (GTRL-G in the VBE)

Put this in and press enter (including the ?):


?Range("B10").value

Tell me what it returns as the result.

Edit: Wait, you have B5 all throughout the code. This could be the problem what is the value in B5?

Edit2:

Just saw your edit, glad I could help

 

[Diogenes]

I'm playing around with this a bit more and I was wondering what the best way would be to modify the code from copying and pasting the whole column to a specific range?

i.e. copy and paste only 165 rows?

The number 165 is another value from a helper cell ("C5").

Is it as simple as adding something along the lines of (Rows(Range("C5").Value).copy) to create some kind of R1C1 array?

Thanks,

 

[Blade Hunter]

Do this in the debug window:

range(cells(1,1).address & ":" & Cells(5,1).address).Address

Notice the result

All you need to do is sub in your formula cells in the Cell address sections.

Cells(1,1) is A1 (Row, Column)

Cells(5,1) is A5

Post back if you get stuck

 

[Diogenes]

I'm getting "Compile error invalid use of property" for the .Address at the end of "range(cells(1,1).address & ":" & Cells(5,1).address).Address"

It seems to work fine when I change it to .copy but I am wondering if that is correct and then how i get that specific range to roll forward and back depending on the variable values in cells "B5" and "C5"?

(I currently have a stop on the second row below):

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

I'm assuming the lines referring to columns will be replaced with code referring to a range based on the variables in "C5" and "B5" in R1C1 format?

Thanks again!

 

[Blade Hunter]

Range(Cells(1, 1).Address & ":" & Cells(5, 1).Address).Copy

You are meant to change the numbers in here with your helper columns for example change 5 to Range("C5").Value

Try and change all the ones you need to. Post back if you get stuck

 

[Diogenes]

Sorry mate - bit of a lag in posting and working.

I had done that.

The code now looks like:

Sub Roll()    Range(Cells(1, 1).Address & ":" & Cells("C5", "B5").Address).Copy Destination:=(Range(Cells(1, 2).Address & ":" & Cells("C5", "B5").Address))
    Range(Cells(1, 1).Address & ":" & Cells("C5", "B5").Address).Copy
    Range(Cells(1, 1).Address & ":" & Cells("C5", "B5").Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Which results in Run-time error 13 "Type Mismatch"

I figure making the R1C1 (1,2) is enough rather than doing the variable cell values +1 as was previous? Or is this causing the mismatch?

 

[Blade Hunter]

Can't use "C5" or "B5" need to say what you want from C5 or B5

Range(Cells(1, 1).Address & ":" & Cells(Range("C5").Value, Range("B5").Value).Address).Copy Destination:=(Range(Cells(1, 2).Address & ":" & Cells(Range("C5").Value, Range("B5").Value).Address))

 

[Diogenes]

Thanks again,

Running and debugging the code I get the following errors:

Run-time error 1004 "Select Method or Range Class Failed".

Run-time error 1004 "Can't open the clipboard".

Run-time error 1004 "We couldn't free up enough space on the Clipboard. Another program might be using it right now"

I have restarted the computer and ran the code with only Excel open and still received these errors.

The code is also very slow now which doesn't make sense as it is 165 Rows and 1 Column that is being copied and pasted - doesn't seem too onerous?

In the code below I have added in all the pieces of code I know of to make the actual code run faster/more smoothly (likely irrelevant overkill I'm sure)

Sub Roll()    Application.CutCopyMode = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
        Range(Cells(1, 1).Address & ":" & Cells(Range("C5").Value, Range("B5").Value).Address).Copy Destination:=(Range(Cells(1, 2).Address & ":" & Cells(Range("C5").Value, Range("B5").Value).Address))
        Range(Cells(1, 1).Address & ":" & Cells(Range("C5").Value, Range("B5").Value).Address).Copy
        Range(Cells(1, 1).Address & ":" & Cells(Range("C5").Value, Range("B5").Value).Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Still I get the same errors which doesn't seem to make sense?