I'd give this a try:
first of all (irrelevant to the problem)renaming the sheet should work as:
Activesheet.name = cVal
as for the problem itself,I suspect there's another error occurring that is NOT
Error 1004.I'd suggest changing the section to:
On Error Resume Next
Err.Clear
ActiveSheet.Name = cVal
If Err.Number <> 0 Then
Debug.Print cVal.Value & " : " & Err.Description
End If
On Error GoTo 0
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Tuesday, April 19, 2016 8:46 PM, noodnutt <noodn...@gmail.com> wrote:
Hi All
This is doing my head in.
I have a range of names:
A 2 Jackson Brown - Musician3 George Cluney - Actor4 Fidelle
Castro - Afficianado5 Someone6 Someone7 Someone8 Someone9 Someone10
Someone11 ETC....
When I execute the following code, it works kind of okay at the very start for
the first couple of cells in my range where it creates a new sheet and renames
it according to the cell Content/Value , but then totally misreads the next
cell Content/Value and creates a default value Sheet.Name "Sheet 3" and then
continues onto the next few cells and everything is fine, but then it will
happen again further down the list and does it 3 times in a row creating
Sheet.Name "Sheets 9 through to 11", then reverts back to creating the sheets.
The range is generally on 19 cell long at the most, and on average only 10,
seriously don't know what's going on. I even inserted 2 Wait Statements to slow
it down in case it was running to quickly not keeping up copying the cell value
for the rename of the sheet.
Sub CreateSheets()
Dim myCsht As Worksheet, mySsht As Worksheet
Dim wbToAddSheetsTo As Excel.Workbook
Dim myFrng As Range, myVrng As Range, cVal As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set myCsht = Sheets("Top 10")
Set mySsht = Sheets("All Tenders")
Set myFrng = mySsht.Range("A1:L" & Rows.Count)
Set myVrng = myCsht.Range("A2:A20")
Set wbToAddSheetsTo = ActiveWorkbook
For Each cVal In myVrng
If Not cVal = "" Then
With wbToAddSheetsTo
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
Sheets(ActiveSheet.Name).Name = cVal
If Err.Number = 1004 Then
Debug.Print cVal.Value & " Sheet Already Exists"
End If
On Error GoTo 0
Application.Wait (Now + TimeValue("00:00:01"))
End With
' Run my Code to do other stuff
End If
Next cVal
End Sub
As always, any assistance is appreciated.
TIAMark
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
