VBA to dynamically decide no of worksheets and perform calculations on them
0
Is there any way with VBA to decide number of worksheets in a workbook and perform calculations on predefined range of cells in those worksheets. The worksheets in the workbook may change as new worksheets may get added.
I have this requirement where worksheets are regularly get added to the workbook and we need to calculate sum of specific range of cells. I googled it but did not find any solution not even here at Stakoveflow
excel vba excel-vba
edited Jan 7 at 8:57
Pᴇʜ
21.7k42750
asked Dec 30 '18 at 15:07
user3405976user3405976
25117
add a comment |
0
Is there any way with VBA to decide number of worksheets in a workbook and perform calculations on predefined range of cells in those worksheets. The worksheets in the workbook may change as new worksheets may get added.
I have this requirement where worksheets are regularly get added to the workbook and we need to calculate sum of specific range of cells. I googled it but did not find any solution not even here at Stakoveflow
excel vba excel-vba
edited Jan 7 at 8:57
Pᴇʜ
21.7k42750
asked Dec 30 '18 at 15:07
user3405976user3405976
25117
1
TryThisWorkbook.Sheets.Count
– PeterT
Dec 30 '18 at 15:22
add a comment |
0
0
0
Is there any way with VBA to decide number of worksheets in a workbook and perform calculations on predefined range of cells in those worksheets. The worksheets in the workbook may change as new worksheets may get added.
I have this requirement where worksheets are regularly get added to the workbook and we need to calculate sum of specific range of cells. I googled it but did not find any solution not even here at Stakoveflow
excel vba excel-vba
edited Jan 7 at 8:57
Pᴇʜ
21.7k42750
asked Dec 30 '18 at 15:07
user3405976user3405976
25117
Is there any way with VBA to decide number of worksheets in a workbook and perform calculations on predefined range of cells in those worksheets. The worksheets in the workbook may change as new worksheets may get added.
I have this requirement where worksheets are regularly get added to the workbook and we need to calculate sum of specific range of cells. I googled it but did not find any solution not even here at Stakoveflow
excel vba excel-vba
excel vba excel-vba
edited Jan 7 at 8:57
Pᴇʜ
21.7k42750
asked Dec 30 '18 at 15:07
user3405976user3405976
25117
edited Jan 7 at 8:57
Pᴇʜ
21.7k42750
asked Dec 30 '18 at 15:07
user3405976user3405976
25117
edited Jan 7 at 8:57
Pᴇʜ
21.7k42750
edited Jan 7 at 8:57
Pᴇʜ
21.7k42750
edited Jan 7 at 8:57
Pᴇʜ
21.7k42750
21.7k42750
asked Dec 30 '18 at 15:07
user3405976user3405976
25117
asked Dec 30 '18 at 15:07
user3405976user3405976
25117
asked Dec 30 '18 at 15:07
user3405976user3405976
25117
25117
1
TryThisWorkbook.Sheets.Count
– PeterT
Dec 30 '18 at 15:22
add a comment |
1
TryThisWorkbook.Sheets.Count
– PeterT
Dec 30 '18 at 15:22
1
1
Try
ThisWorkbook.Sheets.Count– PeterT
Dec 30 '18 at 15:22
Try
ThisWorkbook.Sheets.Count– PeterT
Dec 30 '18 at 15:22
add a comment |
1 Answer
1
active
oldest
votes
1
Loop Through Worksheets With Exceptions List
- Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.
- The difference between the two versions is that the first version uses the object control variable
ws, but the second version doesn't need it, but uses the control variableiand the.Countproperty of theWorksheetscollection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptionsas "".
The For Each Next Approach
Sub WorksheetsForEach()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim ws As Worksheet ' Current Worksheet
Dim vntExceptions As Variant ' Exceptions Array
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For Each ws In Worksheets
With ws
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
The For Next Approach
Sub WorksheetsForNext()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim vntExceptions As Variant ' Exceptions Array
Dim i As Integer ' Worksheets Counter
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For i = 1 To Worksheets.Count
With Worksheets(i)
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
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%2f53978726%2fvba-to-dynamically-decide-no-of-worksheets-and-perform-calculations-on-them%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Loop Through Worksheets With Exceptions List
- Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.
- The difference between the two versions is that the first version uses the object control variable
ws, but the second version doesn't need it, but uses the control variableiand the.Countproperty of theWorksheetscollection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptionsas "".
The For Each Next Approach
Sub WorksheetsForEach()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim ws As Worksheet ' Current Worksheet
Dim vntExceptions As Variant ' Exceptions Array
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For Each ws In Worksheets
With ws
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
The For Next Approach
Sub WorksheetsForNext()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim vntExceptions As Variant ' Exceptions Array
Dim i As Integer ' Worksheets Counter
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For i = 1 To Worksheets.Count
With Worksheets(i)
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
add a comment |
1
Loop Through Worksheets With Exceptions List
- Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.
- The difference between the two versions is that the first version uses the object control variable
ws, but the second version doesn't need it, but uses the control variableiand the.Countproperty of theWorksheetscollection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptionsas "".
The For Each Next Approach
Sub WorksheetsForEach()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim ws As Worksheet ' Current Worksheet
Dim vntExceptions As Variant ' Exceptions Array
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For Each ws In Worksheets
With ws
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
The For Next Approach
Sub WorksheetsForNext()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim vntExceptions As Variant ' Exceptions Array
Dim i As Integer ' Worksheets Counter
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For i = 1 To Worksheets.Count
With Worksheets(i)
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
add a comment |
1
1
1
Loop Through Worksheets With Exceptions List
- Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.
- The difference between the two versions is that the first version uses the object control variable
ws, but the second version doesn't need it, but uses the control variableiand the.Countproperty of theWorksheetscollection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptionsas "".
The For Each Next Approach
Sub WorksheetsForEach()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim ws As Worksheet ' Current Worksheet
Dim vntExceptions As Variant ' Exceptions Array
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For Each ws In Worksheets
With ws
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
The For Next Approach
Sub WorksheetsForNext()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim vntExceptions As Variant ' Exceptions Array
Dim i As Integer ' Worksheets Counter
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For i = 1 To Worksheets.Count
With Worksheets(i)
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
Loop Through Worksheets With Exceptions List
- Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.
- The difference between the two versions is that the first version uses the object control variable
ws, but the second version doesn't need it, but uses the control variableiand the.Countproperty of theWorksheetscollection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptionsas "".
The For Each Next Approach
Sub WorksheetsForEach()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim ws As Worksheet ' Current Worksheet
Dim vntExceptions As Variant ' Exceptions Array
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For Each ws In Worksheets
With ws
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
The For Next Approach
Sub WorksheetsForNext()
' Exceptions Comma-Separated List
Const cExceptions As String = "Sheet1,Sheet2"
Dim vntExceptions As Variant ' Exceptions Array
Dim i As Integer ' Worksheets Counter
Dim j As Integer ' Exceptions Counter
vntExceptions = Split(cExceptions, ",")
For i = 1 To Worksheets.Count
With Worksheets(i)
For j = 0 To UBound(vntExceptions)
If .Name = vntExceptions(j) Then
Exit For
End If
Next
If j > UBound(vntExceptions) Then
' Code in here e.g.
Debug.Print .Name
End If
End With
Next
End Sub
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
edited Dec 30 '18 at 21:17
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
2,5912414
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%2f53978726%2fvba-to-dynamically-decide-no-of-worksheets-and-perform-calculations-on-them%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
1
Try
ThisWorkbook.Sheets.Count– PeterT
Dec 30 '18 at 15:22