Hi, I'm an absolute beginner at visual basic, not a programmer (an if then do specialst) and would be very grateful of any help or views you could give in answering this probem. The code below will rename the current AFPextract01 sheet to AFPextract02, then create a fresh AFPextract01 sheet. This will then increment to 02, 03, etc. with AFPextract01 always being the current sheet.
Current code:
If SheetExists("AFPextract01") Then
intResponse = MsgBox("Sheet AFPextract01 exists, this procedure will rename the existing AFPextract01 and recreate it do you wish to proceed?", vbYesNo + vbQuestion, "Proceed?")
If intResponse = vbNo Then
Exit Sub
End If
rename = False
suffix = 2
Do While rename = False
If SheetExists("AFPextract0" & suffix) Then
suffix = suffix + 1
Else
Sheets("AFPextract01").Select
Sheets("AFPextract01").Name = "AFPextract0" & suffix
rename = True
End If
Loop
End If
Still with me...? Now, some bright spark wants to have the initial sheet called AFPextractA, with the new sheet being created called AFPextractB, then C, etc. Always working off the last sheet (C then D, E etc. as current).
I found what I thought looked like a very useful bit of code on the net (code below) but I don't know how to apply it and I really don't know how it hangs together. As I said, I'm not a programmer, but I'm learning as fast as I can (12 weeks so far).
Useful code maybe?:
Function IncrementTextString(txt As String) As String
Dim L As Integer, i As Integer, c As Integer
Dim S As String
S = txt
L = Len(S)
For i = L To 1 Step -1 'go thru the string, right to left
c = Asc(Mid(S, i, 1)) 'ASCII code of the i-th character
Select Case c
Case 65 To 89, 97 To 121 'A-Y or a-y
S = Left(S, i - 1) & Chr(c + 1) & Mid(S, i + 1)
Exit For
Case 90 'Z
S = Left(S, i - 1) & "A" & Mid(S, i + 1)
Case 122 'z
S = Left(S, i - 1) & "a" & Mid(S, i + 1)
End Select
'in the last two cases, we need to continue the loop:
Next i
If i = 0 Then
IncrementTextString = String(L + 1, 65) 'grow the string
Else
IncrementTextString = S
End If
End Function
Sorry if this is a bit long, but I have more, much more!!!
Current code:
If SheetExists("AFPextract01") Then
intResponse = MsgBox("Sheet AFPextract01 exists, this procedure will rename the existing AFPextract01 and recreate it do you wish to proceed?", vbYesNo + vbQuestion, "Proceed?")
If intResponse = vbNo Then
Exit Sub
End If
rename = False
suffix = 2
Do While rename = False
If SheetExists("AFPextract0" & suffix) Then
suffix = suffix + 1
Else
Sheets("AFPextract01").Select
Sheets("AFPextract01").Name = "AFPextract0" & suffix
rename = True
End If
Loop
End If
Still with me...? Now, some bright spark wants to have the initial sheet called AFPextractA, with the new sheet being created called AFPextractB, then C, etc. Always working off the last sheet (C then D, E etc. as current).
I found what I thought looked like a very useful bit of code on the net (code below) but I don't know how to apply it and I really don't know how it hangs together. As I said, I'm not a programmer, but I'm learning as fast as I can (12 weeks so far).
Useful code maybe?:
Function IncrementTextString(txt As String) As String
Dim L As Integer, i As Integer, c As Integer
Dim S As String
S = txt
L = Len(S)
For i = L To 1 Step -1 'go thru the string, right to left
c = Asc(Mid(S, i, 1)) 'ASCII code of the i-th character
Select Case c
Case 65 To 89, 97 To 121 'A-Y or a-y
S = Left(S, i - 1) & Chr(c + 1) & Mid(S, i + 1)
Exit For
Case 90 'Z
S = Left(S, i - 1) & "A" & Mid(S, i + 1)
Case 122 'z
S = Left(S, i - 1) & "a" & Mid(S, i + 1)
End Select
'in the last two cases, we need to continue the loop:
Next i
If i = 0 Then
IncrementTextString = String(L + 1, 65) 'grow the string
Else
IncrementTextString = S
End If
End Function
Sorry if this is a bit long, but I have more, much more!!!
erlDesignPatterns)[/small]
