Troubleshoot compile error. Overflow:

[pedie]

Hi,

Please help.
Getting compile error.
Compile error
Overflow:
'm running this from sheet2 button.

Thanks
Pedie

Sub try()
Const x As Integer = 1
Const y As Integer = 65500
Const c As Integer = 12
Dim chk As Integer
With Sheets("Sheet1")
For chk = x To y
If .Cells(chk, c).Value = "X" Then
.Cells(chk, c).Value = "Y"
.Cells(chk, c).Offset(0, -10).Resize(, 11).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next chk
End With
End Sub

 

[meldoc]

Hi Pedie

See the link.

http://www.mrexcel.com/forum/showthread.php?t=67603

 

[VoG]

And http://www.ozgrid.com/VBA/variables.htm

Always use Long rather than Integer or Byte. The run time engine converts them to Long anyway.

 

[pedie]

Thanks for the advice.
I have just changed to
x = 1
y = 65500
c = 12
which works ok now.
Can i please ask 1 more queston?
i know i just did lol!
k, can i make

Private Sub Workbook_Open()

code not run if I'm updating this workbook using other book?

 

[VoG]

Yep

Application.EnableEvents=False
'
'open book
'
Application.enableevents=true

 

[pedie]

Thanks again guys!
Thanks Peter, it works perfect!

Brian, thanks!