VBA to dynamically decide no of worksheets and perform calculations on them
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
add a comment |
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
1
TryThisWorkbook.Sheets.Count
– PeterT
Dec 30 '18 at 15:22
add a comment |
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
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
21.7k42750
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
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 variablei
and the.Count
property of theWorksheets
collection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptions
as "".
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
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
});
}
});
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
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 variablei
and the.Count
property of theWorksheets
collection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptions
as "".
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
add a comment |
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 variablei
and the.Count
property of theWorksheets
collection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptions
as "".
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
add a comment |
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 variablei
and the.Count
property of theWorksheets
collection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptions
as "".
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
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 variablei
and the.Count
property of theWorksheets
collection.
- If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave
cExceptions
as "".
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
edited Dec 30 '18 at 21:17
answered Dec 30 '18 at 20:41
VBasic2008VBasic2008
2,5912414
2,5912414
add a comment |
add a comment |
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.
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