Copy Problem

[Syed Haider Ali]

Dear Friends,

I have the following codes to copy specific data from one sheet to
another

Private Sub CommandButton1_Click()
Dim x As Long
Dim y As Long
y = 1
For x = 1 To 65536
If Cells(x, 1).Value >=1245 And Cells(x, 1).Value <= 2000 Then
Cells(x, 1).EntireRow.Copy
Sheets("sheet2").Cells(y, 1).PasteSpecial
y = y + 1
End If
Next x
Application.CutCopyMode = False
End Sub

1. Col A of active sheet has numeric values which are copied from
1245 to 2000 in Sheet2. If Col A contains Date values then above codes
does not work. How can I overcome the problem ?

2. If I create a temp worksheet through userform and then copy from
one sheet to tempws then these codes does not work.

The code for creating temp worksheet are as

Private sub userform_activate()

Dim tempws as worksheet
Set tempws = Thisworkbook.worksheets.add
Tempws.name = “Temp”

End sub

How I can overcome the above two problems

Thanks

Syed Haider Ali

 

[Guest]

#1 sounds like a data type issue...try checking to see if the value of the
referenced cell in numeric before checking to see if it fits the criteria of
the copy loop...i.e:

Private Sub CommandButton1_Click()
Dim x As Long
Dim y As Long
y = 1
For x = 1 To 65536
If isNumeric(Cells(x,1).Value) = true then
If Cells(x, 1).Value >=1245 And Cells(x, 1).Value <= 2000 Then
Cells(x, 1).EntireRow.Copy
Sheets("sheet2").Cells(y, 1).PasteSpecial
y = y + 1
End If
End If
Next x
Application.CutCopyMode = False
End Sub