Mixing
How can I write different information to cells on varying number of sheets from a named range on a summary...
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
Very new to VBA, piecing my code together from previous sheets created by others and what I've found online, working backwards basically.
I have a master workbook I am trying to create. It starts with two worksheets. The first (Start) will house some macros affecting all new worksheets, the second (Template) is a template for generating the remaining worksheets. On Start are a set of two ranges (of varying but equal size), one used to generate the new worksheets from Template; both to write themselves into a specific cell on each of the new worksheets.
Thus far I have managed to:
- Write a sub that generates the new sheets from Template and write from Range 1 on Start into cell "C6" on each of the new sheets. The value of "C6" on Template (1) is equal to cell 1 of the Range 1 on Start, the value of "C6" on Template (2) is equal to cell 2 of the Range 1 on Start, and so on and so forth.
- Write a sub to loop through all new Template worksheets and write a value from Range 2 on Start to cell "E6" on all Template worksheets.
- Write a sub to rename the newly generated worksheets from the values in Range 1 on Start.
What I am struggling with, is how to get the sub that loops through all sheets, to only loop through the sheets named "Template (1)" to "Template (X)" and to write the value from cell 1 of Range 2 to Template (1), cell 2 of Range 2 to Template (2), so on and so forth.
I found code on SO meant to loop through all sheets but one based on sheet name, it does not want to work for this purpose. Additionally I am getting the same value on each of the new worksheets, instead of the Range 2 value that corresponds to it's Range 1 counterpart.
Dim ThisWorkbook As Workbook
Dim recap As Worksheet 'sheet codename for Template
Dim start As Worksheet 'sheet codename for Start
Dim CCell As Range, CSI As Range 'Range 2 is CSI
Set CSI = Sheets("START AND FINISH HERE").Range("CSI")
Set CSI = Range(CSI, CSI.End(xlDown))
For Each CCell In CSI
If CCell <> "" Then
Set recap = Nothing
On Error Resume Next
Set recap = start(CCell.Value)
On Error GoTo 0
If recap Is Nothing Then
Range("E6").Value = CCell.Value
Else
End If
Else
End If
Next CCell
excel vba
asked Jan 3 at 16:47
JohnJohn
12
add a comment |
Very new to VBA, piecing my code together from previous sheets created by others and what I've found online, working backwards basically.
I have a master workbook I am trying to create. It starts with two worksheets. The first (Start) will house some macros affecting all new worksheets, the second (Template) is a template for generating the remaining worksheets. On Start are a set of two ranges (of varying but equal size), one used to generate the new worksheets from Template; both to write themselves into a specific cell on each of the new worksheets.
Thus far I have managed to:
- Write a sub that generates the new sheets from Template and write from Range 1 on Start into cell "C6" on each of the new sheets. The value of "C6" on Template (1) is equal to cell 1 of the Range 1 on Start, the value of "C6" on Template (2) is equal to cell 2 of the Range 1 on Start, and so on and so forth.
- Write a sub to loop through all new Template worksheets and write a value from Range 2 on Start to cell "E6" on all Template worksheets.
- Write a sub to rename the newly generated worksheets from the values in Range 1 on Start.
What I am struggling with, is how to get the sub that loops through all sheets, to only loop through the sheets named "Template (1)" to "Template (X)" and to write the value from cell 1 of Range 2 to Template (1), cell 2 of Range 2 to Template (2), so on and so forth.
I found code on SO meant to loop through all sheets but one based on sheet name, it does not want to work for this purpose. Additionally I am getting the same value on each of the new worksheets, instead of the Range 2 value that corresponds to it's Range 1 counterpart.
Dim ThisWorkbook As Workbook
Dim recap As Worksheet 'sheet codename for Template
Dim start As Worksheet 'sheet codename for Start
Dim CCell As Range, CSI As Range 'Range 2 is CSI
Set CSI = Sheets("START AND FINISH HERE").Range("CSI")
Set CSI = Range(CSI, CSI.End(xlDown))
For Each CCell In CSI
If CCell <> "" Then
Set recap = Nothing
On Error Resume Next
Set recap = start(CCell.Value)
On Error GoTo 0
If recap Is Nothing Then
Range("E6").Value = CCell.Value
Else
End If
Else
End If
Next CCell
excel vba
asked Jan 3 at 16:47
JohnJohn
12
What isstart?
– SJR
Jan 3 at 17:01
I have my starting sheet codenamed start. After that all sheets build off the Template sheet which is currently codenamed recap in VBA.
– John
Jan 3 at 21:27
add a comment |
Very new to VBA, piecing my code together from previous sheets created by others and what I've found online, working backwards basically.
I have a master workbook I am trying to create. It starts with two worksheets. The first (Start) will house some macros affecting all new worksheets, the second (Template) is a template for generating the remaining worksheets. On Start are a set of two ranges (of varying but equal size), one used to generate the new worksheets from Template; both to write themselves into a specific cell on each of the new worksheets.
Thus far I have managed to:
- Write a sub that generates the new sheets from Template and write from Range 1 on Start into cell "C6" on each of the new sheets. The value of "C6" on Template (1) is equal to cell 1 of the Range 1 on Start, the value of "C6" on Template (2) is equal to cell 2 of the Range 1 on Start, and so on and so forth.
- Write a sub to loop through all new Template worksheets and write a value from Range 2 on Start to cell "E6" on all Template worksheets.
- Write a sub to rename the newly generated worksheets from the values in Range 1 on Start.
What I am struggling with, is how to get the sub that loops through all sheets, to only loop through the sheets named "Template (1)" to "Template (X)" and to write the value from cell 1 of Range 2 to Template (1), cell 2 of Range 2 to Template (2), so on and so forth.
I found code on SO meant to loop through all sheets but one based on sheet name, it does not want to work for this purpose. Additionally I am getting the same value on each of the new worksheets, instead of the Range 2 value that corresponds to it's Range 1 counterpart.
Dim ThisWorkbook As Workbook
Dim recap As Worksheet 'sheet codename for Template
Dim start As Worksheet 'sheet codename for Start
Dim CCell As Range, CSI As Range 'Range 2 is CSI
Set CSI = Sheets("START AND FINISH HERE").Range("CSI")
Set CSI = Range(CSI, CSI.End(xlDown))
For Each CCell In CSI
If CCell <> "" Then
Set recap = Nothing
On Error Resume Next
Set recap = start(CCell.Value)
On Error GoTo 0
If recap Is Nothing Then
Range("E6").Value = CCell.Value
Else
End If
Else
End If
Next CCell
excel vba
asked Jan 3 at 16:47
JohnJohn
12
Very new to VBA, piecing my code together from previous sheets created by others and what I've found online, working backwards basically.
I have a master workbook I am trying to create. It starts with two worksheets. The first (Start) will house some macros affecting all new worksheets, the second (Template) is a template for generating the remaining worksheets. On Start are a set of two ranges (of varying but equal size), one used to generate the new worksheets from Template; both to write themselves into a specific cell on each of the new worksheets.
Thus far I have managed to:
- Write a sub that generates the new sheets from Template and write from Range 1 on Start into cell "C6" on each of the new sheets. The value of "C6" on Template (1) is equal to cell 1 of the Range 1 on Start, the value of "C6" on Template (2) is equal to cell 2 of the Range 1 on Start, and so on and so forth.
- Write a sub to loop through all new Template worksheets and write a value from Range 2 on Start to cell "E6" on all Template worksheets.
- Write a sub to rename the newly generated worksheets from the values in Range 1 on Start.
What I am struggling with, is how to get the sub that loops through all sheets, to only loop through the sheets named "Template (1)" to "Template (X)" and to write the value from cell 1 of Range 2 to Template (1), cell 2 of Range 2 to Template (2), so on and so forth.
I found code on SO meant to loop through all sheets but one based on sheet name, it does not want to work for this purpose. Additionally I am getting the same value on each of the new worksheets, instead of the Range 2 value that corresponds to it's Range 1 counterpart.
Dim ThisWorkbook As Workbook
Dim recap As Worksheet 'sheet codename for Template
Dim start As Worksheet 'sheet codename for Start
Dim CCell As Range, CSI As Range 'Range 2 is CSI
Set CSI = Sheets("START AND FINISH HERE").Range("CSI")
Set CSI = Range(CSI, CSI.End(xlDown))
For Each CCell In CSI
If CCell <> "" Then
Set recap = Nothing
On Error Resume Next
Set recap = start(CCell.Value)
On Error GoTo 0
If recap Is Nothing Then
Range("E6").Value = CCell.Value
Else
End If
Else
End If
Next CCell
excel vba
excel vba
asked Jan 3 at 16:47
JohnJohn
12
asked Jan 3 at 16:47
JohnJohn
12
asked Jan 3 at 16:47
JohnJohn
12
asked Jan 3 at 16:47
JohnJohn
12
asked Jan 3 at 16:47
JohnJohn
12
12
What isstart?
– SJR
Jan 3 at 17:01
I have my starting sheet codenamed start. After that all sheets build off the Template sheet which is currently codenamed recap in VBA.
– John
Jan 3 at 21:27
add a comment |
What isstart?
– SJR
Jan 3 at 17:01
I have my starting sheet codenamed start. After that all sheets build off the Template sheet which is currently codenamed recap in VBA.
– John
Jan 3 at 21:27
What is
start?– SJR
Jan 3 at 17:01
What is
start?– SJR
Jan 3 at 17:01
I have my starting sheet codenamed start. After that all sheets build off the Template sheet which is currently codenamed recap in VBA.
– John
Jan 3 at 21:27
I have my starting sheet codenamed start. After that all sheets build off the Template sheet which is currently codenamed recap in VBA.
– John
Jan 3 at 21:27
add a comment |
0
active
oldest
votes
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%2f54026473%2fhow-can-i-write-different-information-to-cells-on-varying-number-of-sheets-from%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f54026473%2fhow-can-i-write-different-information-to-cells-on-varying-number-of-sheets-from%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
start?– SJR
Jan 3 at 17:01
I have my starting sheet codenamed start. After that all sheets build off the Template sheet which is currently codenamed recap in VBA.
– John
Jan 3 at 21:27