sheet selection without knowing name

E

evais

Hello.
I would like to ask for help.I have worksheet where is about 30 sheet.
I need create marco which would change name of individual sheets (
indenpendently on sheet´s previous name ) according to date I put in some
form.
For example:
I have put date 23.10 to 28.10 (number of days = x)
I need macro to remame x sheets.In this case
sheets(1).name = 23.10
sheets(1)name = 24.10.......and so on untill 28.10.


Like this is not problem for me but I need the marco which would function
even the number of day would differ every time.
So there has to be used variant in the index of sheet and not
sheet(1),sheet(2)
but something as sheet (a +b)
Is something like this posibble?
Hopefully you understand what I want my english is not so good.
Thank you.
 
J

Joel

You need a better design before you come up with a good fix. The problem is
wen enumbering worksheets is not to renumber a sheet to a name that already
exists. so firstt you would need to make sure the worksheets are in number
order. Then start by chaning the last sheet first to avoid renaming a sheet
to a name that already exists.

This code MAY WORK

Sub renamesheets()
AddNum = 2
For i = Sheets.Count To 1 Step -1
ShtName = Sheets(i).Name
ShtNum = Left(ShtName, InStr(ShtName, ".") - 1)
'remove first number from shettt name
ShtName = Mid(ShtName, InStr(ShtName, "."))

ShtNum = Val(Trim(ShtNum))
ShtNum = ShtNum + AddNum

ShtName = ShtNum & ShtName
Sheets(i).Name = ShtName
Next i

End Sub
 
R

Rick Rothstein

First off, 23.10 and 28.10 are not **real** dates... there is no year
associated with them. The year would be important if the start date was ever
in February when the ending date was in March (you would need to know the
year to see if there was a February 29th or not). The other reason I am
mentioning this is it will make it easier to do what you want if we have
real dates to work with. Now, assuming you will **always** have enough
worksheets to handle the number of days between your two "dates", consider
code something like this (see the comments inside the code)...

Sub Test()
Dim X As Long
Dim Date1 As Variant
Dim Date2 As Variant
'
' Change the next two lines as indicated
'
Date1 = "23.10" ' Assign this from wherever it is on your form
Date2 = "28.10" ' Assign this from wherever it is on your form
'
Date1 = DateSerial(Year(Now), Right(Date1, 2), Left(Date1, 2))
If Val(Right(Date2, 2)) < Val(Right(Date1, 2)) Then
Date2 = DateSerial(1 + Year(Now), Right(Date2, 2), Left(Date2, 2))
Else
Date2 = DateSerial(Year(Now), Right(Date2, 2), Left(Date2, 2))
End If
For X = 0 To Date2 - Date1
Worksheets(X + 1).Name = Format(Date1 + X, "dd\.mm")
Next
End Sub
 
Top