Move column from one worksheet to another based on active cell

[kellyq]

I have the following code that moves a row to the next blank row on a different worksheet based on the active cell that works great.

Sub Shipped()
'
' Shipped Macro
' Move selected data to Opened Worksheet


Dim rngsource As String
Dim Rngtarget As Range
Dim trow As Integer
Dim Srow As Integer
Dim Lrow As Integer


' set copy range
Srow = ActiveCell.Row
Let rngsource = "a" & Srow & ":" & "ae" & Srow
trow = Worksheets("out2").Cells(Rows.Count, 1).End(xlUp).Row + 1
Set Rngtarget = Worksheets("out2").Range("A" & trow)
Range(rngsource).Copy Destination:=Rngtarget


' delete selected cells
If MsgBox("Move data to Out2?", vbYesNo) = vbNo Then Exit Sub
Range(rngsource).EntireRow.Delete


End Sub

I am trying to get this to work for columns but keep getting an error in the underlined code below.

Sub ShippedII()
' Shipped Macro
' Move selected data to Opened Worksheet
Dim rngsource As String
Dim Rngtarget As Range
Dim tcolumn As Integer
Dim Scolumn As Integer
Dim Lcolumn As Integer


' set copy range
Scolumn = ActiveCell.Column
Let rngsource = Scolumn & "1" & ":" & Scolumn & "30"
tcolumn = Worksheets("out").Cells(1, Columns.Count).End(xlToLeft).Column + 1
Set Rngtarget = Worksheets("out").Range(tcolumn & "1")
Range(rngsource).Copy Destination:=Rngtarget


' delete selected cells
If MsgBox("Move data to Out?", vbYesNo) = vbNo Then Exit Sub
Range(rngsource).EntireColumn.Delete
End Sub


What am I doing wrong? Is there a more efficient way to write this code?

 

[offthelip]

Your problem is because the varaible tcolumn is number, just in the same way the as a bove Trow is a number, however it doesn't work because you are using an addressing mode which expects the column to be defined as a letter not a number, so either you have to convert the number to a letter, or address the range using a numbering system. this is why I always use numbes when addressing ranges in vBa
try changing:

Set Rngtarget = Worksheets("out").Range(tcolumn & "1")

to:

Set Rngtarget = Worksheets("out").Range(cells(1,tcolumn),cells(1,tcolumn ))

 

[Fluff]

Or, as it's a single cell,

Set Rngtarget = Worksheets("out").cells(1,tcolumn)