Hi All
This is doing my head in.
I have a range of names:
A
2 Jackson Brown - Musician
3 George Cluney - Actor
4 Fidelle Castro - Afficianado
5 Someone
6 Someone
7 Someone
8 Someone
9 Someone
10 Someone
11 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.
TIA
Mark
--
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.
