Mixing
1004 application-defined or object-defined error while naming worksheets vba
0
I would like to rename worksheets in an exiting workbook. This is the code i am using:
Dim LobArray As Variant
Dim TypeArray As Variant
Dim g As String
'Added during Edit of question.
Dim NoLobs As Long, NoTypes As Long
Dim l As Long, t As Long, s As Long
Dim SheetNames(100) As String
Dim SheetCountSpL As Long
Dim TmplSpl As Workbook
Set TmplSpl = ThisWorkbook
'-----------------------------
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
NoLobs = UBound(LobArray) - LBound(LobArray) + 1
NoTypes = UBound(TypeArray) - LBound(TypeArray) + 1
For l = LBound(LobArray) To UBound(LobArray)
For t = LBound(TypeArray) To UBound(TypeArray)
SheetNames(l * NoLobs + t) = LobArray(l) & g & TypeArray(t)
Next t
Next l
SheetCountSpL = NoTypes * NoLobs
For s = 1 To SheetCountSpL
TmplSpL.Worksheets(s).Activate
TmplSpL.Worksheets(s).Name = SheetNames(s - 1)
Next s
When i reduce the elements in the LobArray to 3 it works. Basically, when the macro has to rename more then 9 sheets, i get the error i mentioned in the title.
arrays excel vba
edited Nov 20 '18 at 14:26
Darren Bartrup-Cook
13.9k11432
asked Nov 20 '18 at 12:33
frankfrank
1
add a comment |
0
I would like to rename worksheets in an exiting workbook. This is the code i am using:
Dim LobArray As Variant
Dim TypeArray As Variant
Dim g As String
'Added during Edit of question.
Dim NoLobs As Long, NoTypes As Long
Dim l As Long, t As Long, s As Long
Dim SheetNames(100) As String
Dim SheetCountSpL As Long
Dim TmplSpl As Workbook
Set TmplSpl = ThisWorkbook
'-----------------------------
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
NoLobs = UBound(LobArray) - LBound(LobArray) + 1
NoTypes = UBound(TypeArray) - LBound(TypeArray) + 1
For l = LBound(LobArray) To UBound(LobArray)
For t = LBound(TypeArray) To UBound(TypeArray)
SheetNames(l * NoLobs + t) = LobArray(l) & g & TypeArray(t)
Next t
Next l
SheetCountSpL = NoTypes * NoLobs
For s = 1 To SheetCountSpL
TmplSpL.Worksheets(s).Activate
TmplSpL.Worksheets(s).Name = SheetNames(s - 1)
Next s
When i reduce the elements in the LobArray to 3 it works. Basically, when the macro has to rename more then 9 sheets, i get the error i mentioned in the title.
arrays excel vba
edited Nov 20 '18 at 14:26
Darren Bartrup-Cook
13.9k11432
asked Nov 20 '18 at 12:33
frankfrank
1
What is theSheetNamesfunction?
– Darren Bartrup-Cook
Nov 20 '18 at 12:47
Dim SheetNames(100) As String
– frank
Nov 20 '18 at 13:45
I've added theDimstatements for the missing variables - the VBE option for "Require Variable Declaration" makes code easier to debug and highlights variable typos.
– Darren Bartrup-Cook
Nov 20 '18 at 14:20
Providing there's 9 worksheets your code now runs and creates three sheet names per "Lob". So the problem occurs on this line:TmplSpL.Worksheets(s).Name = SheetNames(s - 1)on sheet number 4 when adding another "Lob" which requires 12 worksheets.
– Darren Bartrup-Cook
Nov 20 '18 at 14:38
add a comment |
0
0
0
I would like to rename worksheets in an exiting workbook. This is the code i am using:
Dim LobArray As Variant
Dim TypeArray As Variant
Dim g As String
'Added during Edit of question.
Dim NoLobs As Long, NoTypes As Long
Dim l As Long, t As Long, s As Long
Dim SheetNames(100) As String
Dim SheetCountSpL As Long
Dim TmplSpl As Workbook
Set TmplSpl = ThisWorkbook
'-----------------------------
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
NoLobs = UBound(LobArray) - LBound(LobArray) + 1
NoTypes = UBound(TypeArray) - LBound(TypeArray) + 1
For l = LBound(LobArray) To UBound(LobArray)
For t = LBound(TypeArray) To UBound(TypeArray)
SheetNames(l * NoLobs + t) = LobArray(l) & g & TypeArray(t)
Next t
Next l
SheetCountSpL = NoTypes * NoLobs
For s = 1 To SheetCountSpL
TmplSpL.Worksheets(s).Activate
TmplSpL.Worksheets(s).Name = SheetNames(s - 1)
Next s
When i reduce the elements in the LobArray to 3 it works. Basically, when the macro has to rename more then 9 sheets, i get the error i mentioned in the title.
arrays excel vba
edited Nov 20 '18 at 14:26
Darren Bartrup-Cook
13.9k11432
asked Nov 20 '18 at 12:33
frankfrank
1
I would like to rename worksheets in an exiting workbook. This is the code i am using:
Dim LobArray As Variant
Dim TypeArray As Variant
Dim g As String
'Added during Edit of question.
Dim NoLobs As Long, NoTypes As Long
Dim l As Long, t As Long, s As Long
Dim SheetNames(100) As String
Dim SheetCountSpL As Long
Dim TmplSpl As Workbook
Set TmplSpl = ThisWorkbook
'-----------------------------
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
NoLobs = UBound(LobArray) - LBound(LobArray) + 1
NoTypes = UBound(TypeArray) - LBound(TypeArray) + 1
For l = LBound(LobArray) To UBound(LobArray)
For t = LBound(TypeArray) To UBound(TypeArray)
SheetNames(l * NoLobs + t) = LobArray(l) & g & TypeArray(t)
Next t
Next l
SheetCountSpL = NoTypes * NoLobs
For s = 1 To SheetCountSpL
TmplSpL.Worksheets(s).Activate
TmplSpL.Worksheets(s).Name = SheetNames(s - 1)
Next s
When i reduce the elements in the LobArray to 3 it works. Basically, when the macro has to rename more then 9 sheets, i get the error i mentioned in the title.
arrays excel vba
arrays excel vba
edited Nov 20 '18 at 14:26
Darren Bartrup-Cook
13.9k11432
asked Nov 20 '18 at 12:33
frankfrank
1
edited Nov 20 '18 at 14:26
Darren Bartrup-Cook
13.9k11432
asked Nov 20 '18 at 12:33
frankfrank
1
edited Nov 20 '18 at 14:26
Darren Bartrup-Cook
13.9k11432
edited Nov 20 '18 at 14:26
Darren Bartrup-Cook
13.9k11432
edited Nov 20 '18 at 14:26
Darren Bartrup-Cook
13.9k11432
13.9k11432
asked Nov 20 '18 at 12:33
frankfrank
1
asked Nov 20 '18 at 12:33
frankfrank
1
asked Nov 20 '18 at 12:33
frankfrank
1
1
What is theSheetNamesfunction?
– Darren Bartrup-Cook
Nov 20 '18 at 12:47
Dim SheetNames(100) As String
– frank
Nov 20 '18 at 13:45
I've added theDimstatements for the missing variables - the VBE option for "Require Variable Declaration" makes code easier to debug and highlights variable typos.
– Darren Bartrup-Cook
Nov 20 '18 at 14:20
Providing there's 9 worksheets your code now runs and creates three sheet names per "Lob". So the problem occurs on this line:TmplSpL.Worksheets(s).Name = SheetNames(s - 1)on sheet number 4 when adding another "Lob" which requires 12 worksheets.
– Darren Bartrup-Cook
Nov 20 '18 at 14:38
add a comment |
What is theSheetNamesfunction?
– Darren Bartrup-Cook
Nov 20 '18 at 12:47
Dim SheetNames(100) As String
– frank
Nov 20 '18 at 13:45
I've added theDimstatements for the missing variables - the VBE option for "Require Variable Declaration" makes code easier to debug and highlights variable typos.
– Darren Bartrup-Cook
Nov 20 '18 at 14:20
Providing there's 9 worksheets your code now runs and creates three sheet names per "Lob". So the problem occurs on this line:TmplSpL.Worksheets(s).Name = SheetNames(s - 1)on sheet number 4 when adding another "Lob" which requires 12 worksheets.
– Darren Bartrup-Cook
Nov 20 '18 at 14:38
What is the
SheetNames function?– Darren Bartrup-Cook
Nov 20 '18 at 12:47
What is the
SheetNames function?– Darren Bartrup-Cook
Nov 20 '18 at 12:47
Dim SheetNames(100) As String– frank
Nov 20 '18 at 13:45
Dim SheetNames(100) As String– frank
Nov 20 '18 at 13:45
I've added the
Dim statements for the missing variables - the VBE option for "Require Variable Declaration" makes code easier to debug and highlights variable typos.– Darren Bartrup-Cook
Nov 20 '18 at 14:20
I've added the
Dim statements for the missing variables - the VBE option for "Require Variable Declaration" makes code easier to debug and highlights variable typos.– Darren Bartrup-Cook
Nov 20 '18 at 14:20
Providing there's 9 worksheets your code now runs and creates three sheet names per "Lob". So the problem occurs on this line:
TmplSpL.Worksheets(s).Name = SheetNames(s - 1) on sheet number 4 when adding another "Lob" which requires 12 worksheets.– Darren Bartrup-Cook
Nov 20 '18 at 14:38
Providing there's 9 worksheets your code now runs and creates three sheet names per "Lob". So the problem occurs on this line:
TmplSpL.Worksheets(s).Name = SheetNames(s - 1) on sheet number 4 when adding another "Lob" which requires 12 worksheets.– Darren Bartrup-Cook
Nov 20 '18 at 14:38
add a comment |
2 Answers
2
active
oldest
votes
0
This is the code I use to create and rename sheets. It creates sheets based on selected cells and renames the new sheets accordingly. If sheets exist it deletes them
Sub CreateSheetsFromAList()
Dim MyCell As Range
Dim MyRange As Range
Set MyRange = Selection
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
On Error Resume Next
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet
If Err.Number = 1004 Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0
Next MyCell
End Sub
answered Nov 20 '18 at 14:00
DorinDorin
84
add a comment |
0
This is the error:
LobArray = four elements.TypeArray = three elements.
l = 0,NoLobs = 4,t = 0on first loop.
- First inner loop:
0 * 4 + 0 = 0=SheetNames(0) = LobArray(0) & TypeArray(0) = "Lob1_ea" - Second inner loop:
0 * 4 + 1 = 1=SheetNames(1) = .....
- Third inner loop:
0 * 4 + 2 = 2=SheetNames(2) = ..... - Fourth inner loop:
Doesn't occur asTypeArrayonly has 3 elements.
SheetNames(3) is left blank as a result
- First inner loop:
This code will rename your sheets:
Public Sub Test()
Dim LobArray As Variant
Dim TypeArray As Variant
Dim lobItm As Variant, typeItm As Variant
Dim g As String, x As Long
Dim RequiredSheetCount As Long
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
RequiredSheetCount = (UBound(LobArray) + 1) * (UBound(TypeArray) + 1)
If Worksheets.Count >= RequiredSheetCount Then
For Each lobItm In LobArray
For Each typeItm In TypeArray
x = x + 1
ThisWorkbook.Worksheets(x).Name = lobItm & g & typeItm
Next typeItm
Next lobItm
Else
MsgBox "The workbook needs at least " & RequiredSheetCount & " sheets to work properly."
End If
End Sub
answered Nov 20 '18 at 14:53
Darren Bartrup-CookDarren Bartrup-Cook
13.9k11432
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53393094%2f1004-application-defined-or-object-defined-error-while-naming-worksheets-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
This is the code I use to create and rename sheets. It creates sheets based on selected cells and renames the new sheets accordingly. If sheets exist it deletes them
Sub CreateSheetsFromAList()
Dim MyCell As Range
Dim MyRange As Range
Set MyRange = Selection
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
On Error Resume Next
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet
If Err.Number = 1004 Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0
Next MyCell
End Sub
answered Nov 20 '18 at 14:00
DorinDorin
84
add a comment |
0
This is the code I use to create and rename sheets. It creates sheets based on selected cells and renames the new sheets accordingly. If sheets exist it deletes them
Sub CreateSheetsFromAList()
Dim MyCell As Range
Dim MyRange As Range
Set MyRange = Selection
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
On Error Resume Next
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet
If Err.Number = 1004 Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0
Next MyCell
End Sub
answered Nov 20 '18 at 14:00
DorinDorin
84
add a comment |
0
0
0
This is the code I use to create and rename sheets. It creates sheets based on selected cells and renames the new sheets accordingly. If sheets exist it deletes them
Sub CreateSheetsFromAList()
Dim MyCell As Range
Dim MyRange As Range
Set MyRange = Selection
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
On Error Resume Next
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet
If Err.Number = 1004 Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0
Next MyCell
End Sub
answered Nov 20 '18 at 14:00
DorinDorin
84
This is the code I use to create and rename sheets. It creates sheets based on selected cells and renames the new sheets accordingly. If sheets exist it deletes them
Sub CreateSheetsFromAList()
Dim MyCell As Range
Dim MyRange As Range
Set MyRange = Selection
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
On Error Resume Next
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet
If Err.Number = 1004 Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0
Next MyCell
End Sub
answered Nov 20 '18 at 14:00
DorinDorin
84
answered Nov 20 '18 at 14:00
DorinDorin
84
answered Nov 20 '18 at 14:00
DorinDorin
84
answered Nov 20 '18 at 14:00
DorinDorin
84
84
add a comment |
add a comment |
0
This is the error:
LobArray = four elements.TypeArray = three elements.
l = 0,NoLobs = 4,t = 0on first loop.
- First inner loop:
0 * 4 + 0 = 0=SheetNames(0) = LobArray(0) & TypeArray(0) = "Lob1_ea" - Second inner loop:
0 * 4 + 1 = 1=SheetNames(1) = .....
- Third inner loop:
0 * 4 + 2 = 2=SheetNames(2) = ..... - Fourth inner loop:
Doesn't occur asTypeArrayonly has 3 elements.
SheetNames(3) is left blank as a result
- First inner loop:
This code will rename your sheets:
Public Sub Test()
Dim LobArray As Variant
Dim TypeArray As Variant
Dim lobItm As Variant, typeItm As Variant
Dim g As String, x As Long
Dim RequiredSheetCount As Long
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
RequiredSheetCount = (UBound(LobArray) + 1) * (UBound(TypeArray) + 1)
If Worksheets.Count >= RequiredSheetCount Then
For Each lobItm In LobArray
For Each typeItm In TypeArray
x = x + 1
ThisWorkbook.Worksheets(x).Name = lobItm & g & typeItm
Next typeItm
Next lobItm
Else
MsgBox "The workbook needs at least " & RequiredSheetCount & " sheets to work properly."
End If
End Sub
answered Nov 20 '18 at 14:53
Darren Bartrup-CookDarren Bartrup-Cook
13.9k11432
add a comment |
0
This is the error:
LobArray = four elements.TypeArray = three elements.
l = 0,NoLobs = 4,t = 0on first loop.
- First inner loop:
0 * 4 + 0 = 0=SheetNames(0) = LobArray(0) & TypeArray(0) = "Lob1_ea" - Second inner loop:
0 * 4 + 1 = 1=SheetNames(1) = .....
- Third inner loop:
0 * 4 + 2 = 2=SheetNames(2) = ..... - Fourth inner loop:
Doesn't occur asTypeArrayonly has 3 elements.
SheetNames(3) is left blank as a result
- First inner loop:
This code will rename your sheets:
Public Sub Test()
Dim LobArray As Variant
Dim TypeArray As Variant
Dim lobItm As Variant, typeItm As Variant
Dim g As String, x As Long
Dim RequiredSheetCount As Long
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
RequiredSheetCount = (UBound(LobArray) + 1) * (UBound(TypeArray) + 1)
If Worksheets.Count >= RequiredSheetCount Then
For Each lobItm In LobArray
For Each typeItm In TypeArray
x = x + 1
ThisWorkbook.Worksheets(x).Name = lobItm & g & typeItm
Next typeItm
Next lobItm
Else
MsgBox "The workbook needs at least " & RequiredSheetCount & " sheets to work properly."
End If
End Sub
answered Nov 20 '18 at 14:53
Darren Bartrup-CookDarren Bartrup-Cook
13.9k11432
add a comment |
0
0
0
This is the error:
LobArray = four elements.TypeArray = three elements.
l = 0,NoLobs = 4,t = 0on first loop.
- First inner loop:
0 * 4 + 0 = 0=SheetNames(0) = LobArray(0) & TypeArray(0) = "Lob1_ea" - Second inner loop:
0 * 4 + 1 = 1=SheetNames(1) = .....
- Third inner loop:
0 * 4 + 2 = 2=SheetNames(2) = ..... - Fourth inner loop:
Doesn't occur asTypeArrayonly has 3 elements.
SheetNames(3) is left blank as a result
- First inner loop:
This code will rename your sheets:
Public Sub Test()
Dim LobArray As Variant
Dim TypeArray As Variant
Dim lobItm As Variant, typeItm As Variant
Dim g As String, x As Long
Dim RequiredSheetCount As Long
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
RequiredSheetCount = (UBound(LobArray) + 1) * (UBound(TypeArray) + 1)
If Worksheets.Count >= RequiredSheetCount Then
For Each lobItm In LobArray
For Each typeItm In TypeArray
x = x + 1
ThisWorkbook.Worksheets(x).Name = lobItm & g & typeItm
Next typeItm
Next lobItm
Else
MsgBox "The workbook needs at least " & RequiredSheetCount & " sheets to work properly."
End If
End Sub
answered Nov 20 '18 at 14:53
Darren Bartrup-CookDarren Bartrup-Cook
13.9k11432
This is the error:
LobArray = four elements.TypeArray = three elements.
l = 0,NoLobs = 4,t = 0on first loop.
- First inner loop:
0 * 4 + 0 = 0=SheetNames(0) = LobArray(0) & TypeArray(0) = "Lob1_ea" - Second inner loop:
0 * 4 + 1 = 1=SheetNames(1) = .....
- Third inner loop:
0 * 4 + 2 = 2=SheetNames(2) = ..... - Fourth inner loop:
Doesn't occur asTypeArrayonly has 3 elements.
SheetNames(3) is left blank as a result
- First inner loop:
This code will rename your sheets:
Public Sub Test()
Dim LobArray As Variant
Dim TypeArray As Variant
Dim lobItm As Variant, typeItm As Variant
Dim g As String, x As Long
Dim RequiredSheetCount As Long
g = "_"
LobArray = Array("Lob1", "Lob2", "Lob3", "Lob4")
TypeArray = Array("ea", "pa", "inc")
RequiredSheetCount = (UBound(LobArray) + 1) * (UBound(TypeArray) + 1)
If Worksheets.Count >= RequiredSheetCount Then
For Each lobItm In LobArray
For Each typeItm In TypeArray
x = x + 1
ThisWorkbook.Worksheets(x).Name = lobItm & g & typeItm
Next typeItm
Next lobItm
Else
MsgBox "The workbook needs at least " & RequiredSheetCount & " sheets to work properly."
End If
End Sub
answered Nov 20 '18 at 14:53
Darren Bartrup-CookDarren Bartrup-Cook
13.9k11432
edited Nov 20 '18 at 15:05
answered Nov 20 '18 at 14:53
Darren Bartrup-CookDarren Bartrup-Cook
13.9k11432
answered Nov 20 '18 at 14:53
Darren Bartrup-CookDarren Bartrup-Cook
13.9k11432
answered Nov 20 '18 at 14:53
Darren Bartrup-CookDarren Bartrup-Cook
13.9k11432
13.9k11432
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53393094%2f1004-application-defined-or-object-defined-error-while-naming-worksheets-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is the
SheetNamesfunction?– Darren Bartrup-Cook
Nov 20 '18 at 12:47
Dim SheetNames(100) As String– frank
Nov 20 '18 at 13:45
I've added the
Dimstatements for the missing variables - the VBE option for "Require Variable Declaration" makes code easier to debug and highlights variable typos.– Darren Bartrup-Cook
Nov 20 '18 at 14:20
Providing there's 9 worksheets your code now runs and creates three sheet names per "Lob". So the problem occurs on this line:
TmplSpL.Worksheets(s).Name = SheetNames(s - 1)on sheet number 4 when adding another "Lob" which requires 12 worksheets.– Darren Bartrup-Cook
Nov 20 '18 at 14:38