Cut & Paste with hidden columns

[miker1999]

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

 

[Dave Peterson]

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.

 

[Dave Peterson]

(watch out for linewrap. Sorry)

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.