Cut & Paste with hidden columns
 

Hello,
I have a Workbook with several sheets. When column A on sheet 1 i
change to COMPLETED (via list), I have Worksheet_change code copies th
entire row and moves it to the COMPLETED sheet and then deletes the ro
on sheet 1. The problem I am having is with hidden columns....the
will not copy and I lose whatever data is in those columns. Here is m
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strS
Dim rng As Range
On Error Resume Next
Application.ScreenUpdating = False
If Target.Column = 1 And Target.Count = 1 Then
Select Case Target.Value

Case "COMPLETED"
Set rng = Sheets("COMPLETED").Range("A"
Rows.Count).End(xlUp).Offset(1, 0)
ActiveCell.EntireRow.Copy
rng.PasteSpecial Paste:=xlPasteComments
rng.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.EntireRow.Delete

<there are other cases....I just didn't include them for this example


Case Else
End Select
End If
Application.ScreenUpdating = True
End Sub


Please let me know if these is a better or different way to do this.
The main important pieces are to copy values (not formula), comments
and data in hidden columns.

Thanks in advance.
Mik

--
miker199

-----------------------------------------------------------------------
miker1999's Profile: http://www.excelforum.com/member.php...nfo&userid=475
View this thread: http://www.excelforum.com/showthread.php?threadid=27515

Cut & Paste with hidden columns
 

If you were just pasting values, I think you could just assign the values.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strS
Dim rng As Range
On Error Resume Next
Application.ScreenUpdating = False
If Target.Column = 1 And Target.Count = 1 Then
Select Case UCase(Target.Value)
Case "COMPLETED"
Set rng = Sheets("COMPLETED").Range("A" &
Rows.Count).End(xlUp).Offset(1, 0)
Application.EnableEvents = False
rng.EntireRow.Value = Target.EntireRow.Value
Target.EntireRow.Delete
Application.EnableEvents = True
End Select
End If
Application.ScreenUpdating = True
End Sub

But since you're copying comments too, I think I would unhide the columns, do
the copy|paste and rehide the columns.

You have .screenupdating = false at the top (and probably at the bottom,
too!)--so it shouldn't be too much trouble for the user.



miker1999 wrote:

Hello,
I have a Workbook with several sheets. When column A on sheet 1 is
change to COMPLETED (via list), I have Worksheet_change code copies the
entire row and moves it to the COMPLETED sheet and then deletes the row
on sheet 1. The problem I am having is with hidden columns....they
will not copy and I lose whatever data is in those columns. Here is my
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strS
Dim rng As Range
On Error Resume Next
Application.ScreenUpdating = False
If Target.Column = 1 And Target.Count = 1 Then
Select Case Target.Value

Case "COMPLETED"
Set rng = Sheets("COMPLETED").Range("A" &
Rows.Count).End(xlUp).Offset(1, 0)
ActiveCell.EntireRow.Copy
rng.PasteSpecial Paste:=xlPasteComments
rng.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.EntireRow.Delete

<there are other cases....I just didn't include them for this example

Case Else
End Select
End If
Application.ScreenUpdating = True
End Sub

Please let me know if these is a better or different way to do this.
The main important pieces are to copy values (not formula), comments,
and data in hidden columns.

Thanks in advance.
Mike

--
miker1999

------------------------------------------------------------------------
miker1999's Profile: http://www.excelforum.com/member.php...fo&userid=4757
View this thread: http://www.excelforum.com/showthread...hreadid=275158

--

Dave Peterson

Cut & Paste with hidden columns
 

(watch out for linewrap. Sorry)

Dave Peterson wrote:

If you were just pasting values, I think you could just assign the values.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strS
Dim rng As Range
On Error Resume Next
Application.ScreenUpdating = False
If Target.Column = 1 And Target.Count = 1 Then
Select Case UCase(Target.Value)
Case "COMPLETED"
Set rng = Sheets("COMPLETED").Range("A" &
Rows.Count).End(xlUp).Offset(1, 0)
Application.EnableEvents = False
rng.EntireRow.Value = Target.EntireRow.Value
Target.EntireRow.Delete
Application.EnableEvents = True
End Select
End If
Application.ScreenUpdating = True
End Sub

But since you're copying comments too, I think I would unhide the columns, do
the copy|paste and rehide the columns.

You have .screenupdating = false at the top (and probably at the bottom,
too!)--so it shouldn't be too much trouble for the user.

miker1999 wrote:

Hello,
I have a Workbook with several sheets. When column A on sheet 1 is
change to COMPLETED (via list), I have Worksheet_change code copies the
entire row and moves it to the COMPLETED sheet and then deletes the row
on sheet 1. The problem I am having is with hidden columns....they
will not copy and I lose whatever data is in those columns. Here is my
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strS
Dim rng As Range
On Error Resume Next
Application.ScreenUpdating = False
If Target.Column = 1 And Target.Count = 1 Then
Select Case Target.Value

Case "COMPLETED"
Set rng = Sheets("COMPLETED").Range("A" &
Rows.Count).End(xlUp).Offset(1, 0)
ActiveCell.EntireRow.Copy
rng.PasteSpecial Paste:=xlPasteComments
rng.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.EntireRow.Delete

<there are other cases....I just didn't include them for this example

Case Else
End Select
End If
Application.ScreenUpdating = True
End Sub

Please let me know if these is a better or different way to do this.
The main important pieces are to copy values (not formula), comments,
and data in hidden columns.

Thanks in advance.
Mike

--
miker1999

------------------------------------------------------------------------
miker1999's Profile: http://www.excelforum.com/member.php...fo&userid=4757
View this thread: http://www.excelforum.com/showthread...hreadid=275158

--

Dave Peterson


--

Dave Peterson