It is not clear why on earth you would attempt to rename the sheets within the loop - especially to the same name that they were.
If your sheets are called something other than "Sheet1", "Sheet2" etc then the line(s) like
Sheets("Sheet1").Name = "Sheet1"
will fail. If you really want to do something like that then use
Sheets(1).Name = "Sheet1"
(Note Excel sheets start numbering from 1 not 0 like VB itself)
Again I don't see what the problem is - because you are using a range from Sheet2 in the
For
loop it is getting the values from Sheet2. I'm guessing your problem is that all of the values are being inserted into Cell A5 on Sheet1. If you want to progress down that sheet within the loop then just adjust the Range reference as you go like this
Sub test1_Click()
Dim x As Integer
Start_open = Sheet2.Range("A3").Value
End_open = Sheet2.Range("A5").Value
Sheets(1).Name = "Sheet1"
Z = 5
For x = Start_open To End_open
Range("A" + CStr(Z)).Select
ActiveCell.Value = x
Z = Z + 1
Next
End Sub
If you were trying to put the information somewhere else, on Sheet3 for example then you have to activate the sheet before you can use it.
This line for example
Sheet3.Range("A" + CStr(Z)).Select
results in an "Application-defined or object-defined error" unless you also include
Sheet3.Activate
I hope this helps. If not then use the
Have a Question or Comment? link next to this solution to explain your problem in more detail