Divide sheets among diferent people
So i have multiple sheets with information on them. i need to divide these sheets evenly among multiple people. But i need to divide it by the length of the list. For Example:
Sheet 1 has 15 Rows
Sheet 2 has 41 Rows
Sheet 3 has 32 Rows
Totals Rows is 88 if i have 5 people that would be 17.6 per person. round to the integer would be 17 per person. so what i'm looking for it to do is divide by sheet and row. Example:
Person 1 would get Sheet 1 Rows 1-15 and Sheet 2 Rows 1 and 2
Person 2 would get Sheet 2 Rows 3-20
etc...
here is what i have so far.
Staff = InputBox("How many people will this be divided among?", "Split")
ReDim Person(1 To Staff)
ReDim SNarray(1 To Sheets.Count)
For i = 1 To Sheets.Count
SNarray(i) = Sheets(i).Name
Next
ReDim RowCounts(1 To Sheets.Count)
For i = 1 To Sheets.Count
Sheets(SNarray(i)).Activate
RowCounts(i) = Cells(Rows.Count, 1).End(xlUp).Row
Next
For i = 1 To Sheets.Count
Total = Total + RowCounts(i)
Next
Total = (Total - 2) / Staff
Total = Int(Total)
excel vba excel-vba
asked Nov 20 at 19:51
AmongTheShadows
72
|
show 1 more comment
So i have multiple sheets with information on them. i need to divide these sheets evenly among multiple people. But i need to divide it by the length of the list. For Example:
Sheet 1 has 15 Rows
Sheet 2 has 41 Rows
Sheet 3 has 32 Rows
Totals Rows is 88 if i have 5 people that would be 17.6 per person. round to the integer would be 17 per person. so what i'm looking for it to do is divide by sheet and row. Example:
Person 1 would get Sheet 1 Rows 1-15 and Sheet 2 Rows 1 and 2
Person 2 would get Sheet 2 Rows 3-20
etc...
here is what i have so far.
Staff = InputBox("How many people will this be divided among?", "Split")
ReDim Person(1 To Staff)
ReDim SNarray(1 To Sheets.Count)
For i = 1 To Sheets.Count
SNarray(i) = Sheets(i).Name
Next
ReDim RowCounts(1 To Sheets.Count)
For i = 1 To Sheets.Count
Sheets(SNarray(i)).Activate
RowCounts(i) = Cells(Rows.Count, 1).End(xlUp).Row
Next
For i = 1 To Sheets.Count
Total = Total + RowCounts(i)
Next
Total = (Total - 2) / Staff
Total = Int(Total)
excel vba excel-vba
asked Nov 20 at 19:51
AmongTheShadows
72
I dont see why you need to loop three times over the same sheets.
– Luuklag
Nov 20 at 20:08
How would staff "get" a row? and what's the significance of the-2?
– Tim Williams
Nov 20 at 20:09
So i haven't started to slim it down yet, still new to coding for vba, but i'm just at the start point for it. As for the -2 it is to remove the titles in the first row. so the actually data starts at row 2 on each page.
– AmongTheShadows
Nov 20 at 20:29
For the "get" a row, that will be later on when i populate an email. it would list each sheet and row for each person
– AmongTheShadows
Nov 20 at 20:38
2
Would it be easier here to first combine all the sheets into one?
– extensionhelp
Nov 20 at 20:57
|
show 1 more comment
So i have multiple sheets with information on them. i need to divide these sheets evenly among multiple people. But i need to divide it by the length of the list. For Example:
Sheet 1 has 15 Rows
Sheet 2 has 41 Rows
Sheet 3 has 32 Rows
Totals Rows is 88 if i have 5 people that would be 17.6 per person. round to the integer would be 17 per person. so what i'm looking for it to do is divide by sheet and row. Example:
Person 1 would get Sheet 1 Rows 1-15 and Sheet 2 Rows 1 and 2
Person 2 would get Sheet 2 Rows 3-20
etc...
here is what i have so far.
Staff = InputBox("How many people will this be divided among?", "Split")
ReDim Person(1 To Staff)
ReDim SNarray(1 To Sheets.Count)
For i = 1 To Sheets.Count
SNarray(i) = Sheets(i).Name
Next
ReDim RowCounts(1 To Sheets.Count)
For i = 1 To Sheets.Count
Sheets(SNarray(i)).Activate
RowCounts(i) = Cells(Rows.Count, 1).End(xlUp).Row
Next
For i = 1 To Sheets.Count
Total = Total + RowCounts(i)
Next
Total = (Total - 2) / Staff
Total = Int(Total)
excel vba excel-vba
asked Nov 20 at 19:51
AmongTheShadows
72
So i have multiple sheets with information on them. i need to divide these sheets evenly among multiple people. But i need to divide it by the length of the list. For Example:
Sheet 1 has 15 Rows
Sheet 2 has 41 Rows
Sheet 3 has 32 Rows
Totals Rows is 88 if i have 5 people that would be 17.6 per person. round to the integer would be 17 per person. so what i'm looking for it to do is divide by sheet and row. Example:
Person 1 would get Sheet 1 Rows 1-15 and Sheet 2 Rows 1 and 2
Person 2 would get Sheet 2 Rows 3-20
etc...
here is what i have so far.
Staff = InputBox("How many people will this be divided among?", "Split")
ReDim Person(1 To Staff)
ReDim SNarray(1 To Sheets.Count)
For i = 1 To Sheets.Count
SNarray(i) = Sheets(i).Name
Next
ReDim RowCounts(1 To Sheets.Count)
For i = 1 To Sheets.Count
Sheets(SNarray(i)).Activate
RowCounts(i) = Cells(Rows.Count, 1).End(xlUp).Row
Next
For i = 1 To Sheets.Count
Total = Total + RowCounts(i)
Next
Total = (Total - 2) / Staff
Total = Int(Total)
excel vba excel-vba
excel vba excel-vba
asked Nov 20 at 19:51
AmongTheShadows
72
asked Nov 20 at 19:51
AmongTheShadows
72
asked Nov 20 at 19:51
AmongTheShadows
72
asked Nov 20 at 19:51
AmongTheShadows
72
asked Nov 20 at 19:51
AmongTheShadows
72
72
I dont see why you need to loop three times over the same sheets.
– Luuklag
Nov 20 at 20:08
How would staff "get" a row? and what's the significance of the-2?
– Tim Williams
Nov 20 at 20:09
So i haven't started to slim it down yet, still new to coding for vba, but i'm just at the start point for it. As for the -2 it is to remove the titles in the first row. so the actually data starts at row 2 on each page.
– AmongTheShadows
Nov 20 at 20:29
For the "get" a row, that will be later on when i populate an email. it would list each sheet and row for each person
– AmongTheShadows
Nov 20 at 20:38
2
Would it be easier here to first combine all the sheets into one?
– extensionhelp
Nov 20 at 20:57
|
show 1 more comment
I dont see why you need to loop three times over the same sheets.
– Luuklag
Nov 20 at 20:08
How would staff "get" a row? and what's the significance of the-2?
– Tim Williams
Nov 20 at 20:09
So i haven't started to slim it down yet, still new to coding for vba, but i'm just at the start point for it. As for the -2 it is to remove the titles in the first row. so the actually data starts at row 2 on each page.
– AmongTheShadows
Nov 20 at 20:29
For the "get" a row, that will be later on when i populate an email. it would list each sheet and row for each person
– AmongTheShadows
Nov 20 at 20:38
2
Would it be easier here to first combine all the sheets into one?
– extensionhelp
Nov 20 at 20:57
I dont see why you need to loop three times over the same sheets.
– Luuklag
Nov 20 at 20:08
I dont see why you need to loop three times over the same sheets.
– Luuklag
Nov 20 at 20:08
How would staff "get" a row? and what's the significance of the
-2 ?– Tim Williams
Nov 20 at 20:09
How would staff "get" a row? and what's the significance of the
-2 ?– Tim Williams
Nov 20 at 20:09
So i haven't started to slim it down yet, still new to coding for vba, but i'm just at the start point for it. As for the -2 it is to remove the titles in the first row. so the actually data starts at row 2 on each page.
– AmongTheShadows
Nov 20 at 20:29
So i haven't started to slim it down yet, still new to coding for vba, but i'm just at the start point for it. As for the -2 it is to remove the titles in the first row. so the actually data starts at row 2 on each page.
– AmongTheShadows
Nov 20 at 20:29
For the "get" a row, that will be later on when i populate an email. it would list each sheet and row for each person
– AmongTheShadows
Nov 20 at 20:38
For the "get" a row, that will be later on when i populate an email. it would list each sheet and row for each person
– AmongTheShadows
Nov 20 at 20:38
2
2
Would it be easier here to first combine all the sheets into one?
– extensionhelp
Nov 20 at 20:57
Would it be easier here to first combine all the sheets into one?
– extensionhelp
Nov 20 at 20:57
|
show 1 more comment
1 Answer
1
active
oldest
votes
Try
Sub test()
Dim Ws As Worksheet
Dim r As Long, i As Integer, Staff As Integer
Staff = InputBox("How many people will this be divided among?", "Split")
For i = 1 To Staff
Set Ws = Sheets(i)
With Ws
r = .Range("a" & Rows.Count).End(xlUp).Row
Total = Total + r - 1
End With
Next
Total = Total / Staff
Total = Int(Total)
MsgBox Total
End Sub
answered Nov 21 at 0:03
Dy.Lee
3,275159
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%2f53400554%2fdivide-sheets-among-diferent-people%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
Try
Sub test()
Dim Ws As Worksheet
Dim r As Long, i As Integer, Staff As Integer
Staff = InputBox("How many people will this be divided among?", "Split")
For i = 1 To Staff
Set Ws = Sheets(i)
With Ws
r = .Range("a" & Rows.Count).End(xlUp).Row
Total = Total + r - 1
End With
Next
Total = Total / Staff
Total = Int(Total)
MsgBox Total
End Sub
answered Nov 21 at 0:03
Dy.Lee
3,275159
add a comment |
Try
Sub test()
Dim Ws As Worksheet
Dim r As Long, i As Integer, Staff As Integer
Staff = InputBox("How many people will this be divided among?", "Split")
For i = 1 To Staff
Set Ws = Sheets(i)
With Ws
r = .Range("a" & Rows.Count).End(xlUp).Row
Total = Total + r - 1
End With
Next
Total = Total / Staff
Total = Int(Total)
MsgBox Total
End Sub
answered Nov 21 at 0:03
Dy.Lee
3,275159
add a comment |
Try
Sub test()
Dim Ws As Worksheet
Dim r As Long, i As Integer, Staff As Integer
Staff = InputBox("How many people will this be divided among?", "Split")
For i = 1 To Staff
Set Ws = Sheets(i)
With Ws
r = .Range("a" & Rows.Count).End(xlUp).Row
Total = Total + r - 1
End With
Next
Total = Total / Staff
Total = Int(Total)
MsgBox Total
End Sub
answered Nov 21 at 0:03
Dy.Lee
3,275159
Try
Sub test()
Dim Ws As Worksheet
Dim r As Long, i As Integer, Staff As Integer
Staff = InputBox("How many people will this be divided among?", "Split")
For i = 1 To Staff
Set Ws = Sheets(i)
With Ws
r = .Range("a" & Rows.Count).End(xlUp).Row
Total = Total + r - 1
End With
Next
Total = Total / Staff
Total = Int(Total)
MsgBox Total
End Sub
answered Nov 21 at 0:03
Dy.Lee
3,275159
answered Nov 21 at 0:03
Dy.Lee
3,275159
answered Nov 21 at 0:03
Dy.Lee
3,275159
answered Nov 21 at 0:03
Dy.Lee
3,275159
3,275159
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53400554%2fdivide-sheets-among-diferent-people%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
I dont see why you need to loop three times over the same sheets.
– Luuklag
Nov 20 at 20:08
How would staff "get" a row? and what's the significance of the
-2?– Tim Williams
Nov 20 at 20:09
So i haven't started to slim it down yet, still new to coding for vba, but i'm just at the start point for it. As for the -2 it is to remove the titles in the first row. so the actually data starts at row 2 on each page.
– AmongTheShadows
Nov 20 at 20:29
For the "get" a row, that will be later on when i populate an email. it would list each sheet and row for each person
– AmongTheShadows
Nov 20 at 20:38
2
Would it be easier here to first combine all the sheets into one?
– extensionhelp
Nov 20 at 20:57