change

Sheet1.Activate
PullRng.Select
Selection.Cut
Sheet19.Select
PasteRng.Select
ActiveSheet.Paste
Sheet1.Select 'or activate, tried both
PullRng.Select '**** This is where it selects
Sheet1.Range(PstRow & ":" & PstRow)

Selection.Delete Shift:=xlUp

to
pullrng.copy PasteRng
pullrng.EntireRow.Delete

--
Regards,
Tom Ogilvy



"KR" wrote:

In the code below, it works great until I return to Sheet1 to delete the
selected row. For some reason, when I return to Sheet1, the command
PullRng.Select actually selects a range I haven't even defined (a
combination of PullRng and PasteRng; the sheet from PullRng and the row from
PasteRng).

So basically, it goes to PullRng, selects the correct range, copies it,
selects PasteRng on Sheet19, and pastes it in the right row. It is only when
it returns to Sheet1 to delete that row, that it goes whacky and selects the
equivalent of PasteRng Row, e,g, 2, then 3, then 4...

Am I doing something wrong? Is this a bug? Either way, what should I do
differently?

Thanks,
Keith



Private Sub CommandButton1_Click()

frmMove.Hide 'hide so I can switch to Excel and see the sheet to tell what
is actually happening during development

Dim PullRng As Range
Dim PasteRng As Range

LastOutputMUV = Sheet1.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
FirstClearDestRow = 1 + Sheet19.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row

Dim ctl As Control
For Each ctl In frmMove.Controls
If TypeOf ctl Is MSForms.CheckBox Then 'from a userform that has the
12 months to determine what months of data to copy
If ctl.Value = True Then 'user wants to move this month
'MsgBox ctl.Tag 'shows which month(s) are selected,
'each chkbox tag is set to the
number equivalent of that month, Jan=1, Feb=2, etc

For n = 3 To LastOutputMUV 'cycle through all the source records
If Month(Sheet1.Range("A" & Trim(Str(n))).Value) = ctl.Tag And _
Year(Sheet1.Range("A" & Trim(Str(n))).Value) = 2006 Then 'for
the records that match
'convert my numerics to text to simplify my set range
statements
ActRow = Trim(Str(n))
PstRow = Trim(Str(FirstClearDestRow))
'set the two ranges
Set PullRng = Sheet1.Range(ActRow & ":" & ActRow) 'note this
is Sheet1
Set PasteRng = Sheet19.Range(PstRow & ":" & PstRow) 'note
this is Sheet19
'copy the row to my destination sheet and delete the row
from the source sheet
Sheet1.Activate
PullRng.Select
Selection.Cut
Sheet19.Select
PasteRng.Select
ActiveSheet.Paste
Sheet1.Select 'or activate, tried both
PullRng.Select '**** This is where it selects
Sheet1.Range(PstRow & ":" & PstRow)
'which I never did in code! PstRow
is for Sheet19....
Selection.Delete Shift:=xlUp
FirstClearDestRow = FirstClearDestRow + 1
n = n - 1
End If
Next

End If
End If
Next

End Sub