Dechiper existing VBA code and implement stop in the correct area

Has now been solved - thanks for looking

All,

Again, thanks for your assistance in advance - especially to those who can provide a solution to my answer.

This following code was written by a colleague who has since left the company - I want to utilise the spreadsheet and VBA code to suit my own needs.

Sub numbers()
Dim r1, r2, bm, sname, x, drow, daynum, y, cycle
'Application.ScreenUpdating = False
For daynum = 1 To 5
sname = Sheets("Control").Range("C" & daynum + 1)
For x = 1 To 8
r1 = Sheets("Control").Range("B" & x + 9)
r2 = Sheets("Control").Range("C" & x + 9)
drow = Sheets("Control").Cells(x + 9, daynum + 3)
cycle = 1
Do
Sheets(sname).Select
If cycle = 1 Then
Range("D" & r1 & ":AT" & r1).Copy
Else
If Sheets("Control").Range("B" & daynum + 1) <> "Sat" Then
Range("G" & r2 & ":X" & r2).Copy
End If
End If
Sheets("Numbers").Select
If cycle = 1 Then
Range("B" & drow).Select
Else
If Sheets("Control").Range("B" & daynum + 1) <> "Sat" Then
Range("W" & drow).Select
End If
End If

If Sheets("Control").Range("B" & daynum + 1) = "Sat" And cycle = 2 Then
Else
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
y = 3
dc = 0
Do
Cells(drow, y).Select
If Selection = "" Then
Selection.Delete Shift:=xlToLeft
y = y - 1
dc = dc + 1
End If
y = y + 1
Loop Until y = 35 Or dc > 30
cycle = cycle + 1
Loop Until cycle = 3

End Sub

Attached is a copy of the Control worksheet that it's reading the data from.

All I need is to implement a suitable stop in this bit of code to effectively stop the VBA looping and feeding in data for all the days. I just want the code to feed data in for the Monday, stop, so that I can change the Block 1 and 2 row numbers and start again to pull through data for Tuesday, stop and repeat again for Wednesday, Thursday, etc.

The code does work, surprisingly - I'm relatively new to VBA but I grasp the mechanics upto a point, well until it gets too
End If
Sheets("Numbers").Select
If cycle = 1 Then
Range("B" & drow).Select

After that I'm at a loss.

Feel free to implement changes to simplify and improve the code if necessary, but please explain the changes.

Thanks again.

Chris

I wonder whether you were able to run the macro successfully
there are some errors when you start to run on your file
assuming macro modified to avoid those erros there is another difficulty

in the first loop

Please Login or Register  to view this content.

if you add another statement

Please Login or Register  to view this content.

you will get sname as "Source Mon"
then further down

Please Login or Register  to view this content.

that mean you want to select a sheet called "source MON"
there is no such sheet in your file.
do you want to add a new sheet and call it "source Mon"

as it is in a loop(

Please Login or Register  to view this content.

) you want to open many more sheet for each day.


So it is better if you tell what you want to do with the sheet "control" somebody may be able to correct your macro and also explain the statements.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here