PDA

View Full Version : Move Column to next tab



Cell Block
03-26-2010, 09:39 AM
Hi,

I've seen a lot of posts about moving rows to the next tab, but none on columns. I have tried changing code from rows to columns, but have not been able to get anything to work.

Based on a data validation cell being changed from "Active" to "Inactive" in Row 3 of the column, I would like any column changed to Inactive to be moved to the next tab, a sheet is named "Archive". First column is F, and range is F2:F1202, if that helps.

mbarron
03-26-2010, 10:08 AM
Try this:
Sub moveIt()
Dim i As Long
i = 2
Do Until Cells(1, i) = ""
If Cells(3, i) = "Inactive" Then
Cells(1, i).EntireColumn.Copy Destination:=Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
End If
i = i + 1
Loop
End Sub

Cell Block
03-26-2010, 11:20 AM
Thanks for the update! However, I guess due to my lack of knowledge of VBA and how it works and inability to explain my needs better, I failed to mention that this should be an event driven macro.

Currently your code isn't working, and I am unsure why.

mbarron
03-26-2010, 11:39 AM
The following will copy the column to Sheet2 if the value in row 3 of the column is changed to Inactive. Remove the comment to have the row that has been copied deleted from the original sheet.

Right click on the tab of the sheet and choose View Code. Paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Rows("3:3"), Target) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "Inactive" Then
Cells(1, Target.Column).EntireColumn.Copy _
Destination:=Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
' Cells(1, Target.Column).EntireColumn.Delete
Application.EnableEvents = True

End If
End If

End Sub