I think I've got it.
Rather than just tell you, let me try to explain "why" it's doing what it's not
doing... ??
Have you noticed that if you "manually" copy and paste cells,
you first select the cells and hit "ctrl-C" or the menu version.
Excel "highlights" the cells with an animated border.
You then select the destination and hit "ctrl-v" and it pastes the cells.
Notice that the original cells are still "highlighted"?
Now, say you decide to first clear the destination cells.
When you select them and hit "delete", the highlighting of the original cells
stops?
and if you try to paste, there's nothing in the copy buffer!
I think the reason is that you're really copying to an Excel "clipboard". not a
Windows clipboard.
I don't think the Excel clipboard is actually storing data (and formatting, and
fonts, and color, etc...)
I think it stores a REFERENCE... then clears the reference when Excel thinks it
is no longer needed.
This is what you're doing.
You first filter the data and select it.
then you go to the destination sheet, and clear the contents.
this empties the buffer!
Clear the destination first.
You don't have to actually SELECT the sheet.
You can use:
Sheets("working").Cells.ClearContents
then, be sure to remove anything "extra" from between the copy/paste activity.
you can simplify the macro like:
Public Sub sperecret()
Sheets("working").Visible = True
Sheets("working").Cells.ClearContents
Sheets("Main").Select
Selection.AutoFilter Field:=3, Criteria1:=UserForm1.ComboBox1.Text
Cells.Select
Selection.Copy
Sheets("working").Range("A1").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("working").Select
Range("A1").Select 'Changed due to error if only (1) record is returned
Range(Selection, Selection.End(xlDown)).Select
rcount = Selection.Count
totscr1 = (rcount / 10)
totscr2 = (rcount Mod 10)
If totscr2 <> 0 Then
totscr = totscr1 + 1
Else
totscr = totscr1
End If
Sheets("working").Visible = False
Sheets("Main").Select
Selection.AutoFilter Field:=2
End Sub