problem copying from one sheet to another

[liam1289]

Iv a problem with my vb code.i want the user to enter a room number and me to copy and paste the class into a new workbook but it wont run..can anyone help



Private Sub CommandButton2_Click()
Dim Class, room As String
Dim block, q As Integer
Dim r As Range
Const TotalTimes = 9

room = InputBox("Please enter a room")
Set r = Cells.Find(What:=room)
r.Activate
For Each ws In ActiveWorkbook.Worksheets
With ws
For block = 1 To TotalTimes
r.Activate
Set r = r.Offset(1, 0)
r.Activate
Selection.Copy
ChDir "F:\New Folder (3)"
Workbooks.Open Filename:="F:\New Folder (3)\room.XLS"
Windows("Room.XLS").Activate
ActiveSheet.Paste

Windows("Final.XLS").Activate

Next block

End With
Next ws
End Sub

 

[texasalynn]

you haven't defined TotalTimes
edit: oops missed it

what happens? Try stepping through the code and see where it is not working as expected