Move values to make a list
 

I need to take the cells in column Q, R, S, and T and move them t
column U, V, W, and X respectively. The only problem is not every cel
has values, but they all have formulas. I only want the cells that hav
values to be moved. A formula would be best so it does it automatically
but I need the UVWX columns to change if I change something in the QRS
columns. The row range is 5-3060

--
Optitro
-----------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=47333

Move values to make a list
 

right click on the sheet tab, select view code and put in code like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 17 And Target.Column <= 20 Then
If Not Target.HasFormula Then
Application.EnableEvents = False
Target.Offset(0, 4).Value = Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub


Assumes no merged cells in the affected columns and the sheet isn't
protected.


--
Regards,
Tom Ogilvy

"Optitron" wrote in
message ...

I need to take the cells in column Q, R, S, and T and move them to
column U, V, W, and X respectively. The only problem is not every cell
has values, but they all have formulas. I only want the cells that have
values to be moved. A formula would be best so it does it automatically,
but I need the UVWX columns to change if I change something in the QRST
columns. The row range is 5-3060.


--
Optitron
------------------------------------------------------------------------
Optitron's Profile:
http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=473330

Move values to make a list
 

I've never used a code like this. How do you activate it?

Tom Ogilvy Wrote:
right click on the sheet tab, select view code and put in code like
this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 17 And Target.Column <= 20 Then
If Not Target.HasFormula Then
Application.EnableEvents = False
Target.Offset(0, 4).Value = Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub


Assumes no merged cells in the affected columns and the sheet isn't
protected.


--
Regards,
Tom Ogilvy


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=473330

Move values to make a list
 

right click on the sheet tab, select view code and put in code like this

--
Regards,
Tom Ogilvy

"Optitron" wrote in
message ...

I've never used a code like this. How do you activate it?

Tom Ogilvy Wrote:
right click on the sheet tab, select view code and put in code like
this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 17 And Target.Column <= 20 Then
If Not Target.HasFormula Then
Application.EnableEvents = False
Target.Offset(0, 4).Value = Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub


Assumes no merged cells in the affected columns and the sheet isn't
protected.


--
Regards,
Tom Ogilvy


--
Optitron
------------------------------------------------------------------------
Optitron's Profile:
http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=473330

Move values to make a list
 

Now I see what happened. It only does it if I type something into QRST
I have formulas there that result in data from other cells, so since i
was already there it didn't move over. When I do type something int
those cells it moves it over but not up. So what I need is the cells i
QRST to move over and up assuming that the data is already there s
there are no spaces. Basically what I have is a list of tools, if th
tool is broken it moves the info to QRST the I have 3000 rows that hav
data or not and I want all the data in an easier format with no spaces
I'm doing this the hard way for now since there is probably an easie
way but i'm still learning

--
Optitro
-----------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=47333

Move values to make a list
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 17 And Target.Column <= 20 Then
If Not Target.HasFormula Then
Application.EnableEvents = False
set rng = cells(rows.count,Target.Offset(0,4).Column).End(xl up)(2)
rng.value = Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"Optitron" wrote in
message ...

Now I see what happened. It only does it if I type something into QRST.
I have formulas there that result in data from other cells, so since it
was already there it didn't move over. When I do type something into
those cells it moves it over but not up. So what I need is the cells in
QRST to move over and up assuming that the data is already there so
there are no spaces. Basically what I have is a list of tools, if the
tool is broken it moves the info to QRST the I have 3000 rows that have
data or not and I want all the data in an easier format with no spaces.
I'm doing this the hard way for now since there is probably an easier
way but i'm still learning.


--
Optitron
------------------------------------------------------------------------
Optitron's Profile:
http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=473330