Move column from one worksheet to another based on active cell

kellyq

New Member
Joined
Jan 16, 2018
Messages
1
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?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,738
Office Version
  1. 2010
Platform
  1. Windows
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:

Code:
Set Rngtarget = Worksheets("out").Range(tcolumn & "1")
to:
Code:
Set Rngtarget = Worksheets("out").Range(cells(1,tcolumn),cells(1,tcolumn ))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Or, as it's a single cell,
Code:
Set Rngtarget = Worksheets("out").cells(1,tcolumn)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,778
Members
416,202
Latest member
donya ba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top