Auto-populate a sheet with data from different other sheets
Solved/Closed
ThanatosTalos
Blocked Profile - Jul 29, 2019 at 07:07 AM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Blocked Profile - Jul 29, 2019 at 07:07 AM
Hello,
I have been having problems with having a general data sheet in my workbook. My problem is as follows:
I have 3 different sheets, all of which keep track of the same set of data, for purposes of this question, let's say they all keep track of:
-Id
-Name
-Random Number
They are separated in 3 different sheets because they are filled by 3 different people, as the workbook is in a network. However, the problem that I am facing is having an auto filling general sheet; as in, when the first user fills rows in their respective sheet, it auto populates in a fourth different sheet, where all the data is kept track of.
Maybe I am not making myself clear as English is not my first language, but in other words, I want a sheet to be auto populated as multiple others are filled.
I have been having problems with having a general data sheet in my workbook. My problem is as follows:
I have 3 different sheets, all of which keep track of the same set of data, for purposes of this question, let's say they all keep track of:
-Id
-Name
-Random Number
They are separated in 3 different sheets because they are filled by 3 different people, as the workbook is in a network. However, the problem that I am facing is having an auto filling general sheet; as in, when the first user fills rows in their respective sheet, it auto populates in a fourth different sheet, where all the data is kept track of.
Maybe I am not making myself clear as English is not my first language, but in other words, I want a sheet to be auto populated as multiple others are filled.
System Configuration: Windows / Chrome 75.0.3770.142
Related:
- How to auto populate data from multiple sheets to a master
- How to create a master sheet from multiple sheets in excel - Best answers
- How do you auto populate data from multiple sheets to a master? - Best answers
- How do you auto populate data from multiple sheets to a master? - Guide
- How to fill multiple Excel sheets from master sheet - Guide
- Auto populate data between Excel sheets ✓ - Forum - Excel
- Auto-populate info. from master sheet to multiple sheets ✓ - Forum - Excel
- Autopopulating multiple sheets from one main sheet ✓ - Forum - Excel
9 replies
Ok, this is real easy. Make your cell reference to the the sheet, as in
=Sheet4!A2
=Sheet4!A3
=Sheet4!A4
and then....
=Sheet3!A78
=Sheet3!A79
=Sheet3!A80
=Sheet3!A81
No coding needed!
=Sheet4!A2
=Sheet4!A3
=Sheet4!A4
and then....
=Sheet3!A78
=Sheet3!A79
=Sheet3!A80
=Sheet3!A81
No coding needed!
Here is code that will do it...
ThanatosTalos
Jul 23, 2019 at 03:17 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 23, 2019 at 03:17 PM
Well, let me try out, i was doing it the other way, I will try with the code
ThanatosTalos
Jul 23, 2019 at 03:18 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 23, 2019 at 03:18 PM
One second, Should i put this code on the master sheet? or each of the children one?
ThanatosTalos
Jul 23, 2019 at 03:25 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 23, 2019 at 03:25 PM
ohhh I understand now, let me try it out right now please, and thank you in advance, it's very nice of you
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub Combinesheets()
'this code assumes your master sheet is on the first sheet 'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim tabcounter, rowcounter
Dim thevalue
tabcount = ThisWorkbook.Worksheets.Count
For tabcounter = 2 To tabcount
For rowcounter = 1 To findlastrow(tabcounter)
thevalue = ThisWorkbook.Worksheets(tabcounter).Range("A" & rowcounter).Value
ThisWorkbook.Worksheets(1).Range("A" & findlastrow(1) + 1).Value = thevalue
Next
Next
End Sub
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub Combinesheets()
'this code assumes your master sheet is on the first sheet 'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim tabcounter, rowcounter
Dim thevalue
tabcount = ThisWorkbook.Worksheets.Count
For tabcounter = 2 To tabcount
For rowcounter = 1 To findlastrow(tabcounter)
thevalue = ThisWorkbook.Worksheets(tabcounter).Range("A" & rowcounter).Value
ThisWorkbook.Worksheets(1).Range("A" & findlastrow(1) + 1).Value = thevalue
Next
Next
End Sub
ThanatosTalos
Jul 23, 2019 at 03:45 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 23, 2019 at 03:45 PM
It works great! thank you so much! Now i have two small issues though; first off, how can i modify the code for it run in a set of specific sheets, instead of all of them, and also, when the sheets get combined, they do so in columns, instead of rows; meaning that the master sheet shows all the information in a single column going down, instead of across rows
OK, in order to run only on specific sheets, we can build an array to hold the sheets that need to be run.
So, we would do something like this:
Let me pivot the list, and lay it out now. Is there a LAST Column that is standard for each line, or do we need to consider each line possibly having more or less than the previous one?
So, we would do something like this:
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub Combinesheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim sheetsList()
sheetsList() = [{"Sheet2","Sheet4"}] ' place your sheet names here
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, 1).Value
ThisWorkbook.Worksheets(1).Range("A" & findlastrow(1) + 1).Value = thevalue
Next
Next
End Sub
Let me pivot the list, and lay it out now. Is there a LAST Column that is standard for each line, or do we need to consider each line possibly having more or less than the previous one?
ThanatosTalos
Jul 23, 2019 at 04:54 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 23, 2019 at 04:54 PM
There is a last column that is standard for every single sheet, althought the actual value in the cell changes, as they all contain the same info, and they always have to have the same amount of columns
ThanatosTalos
Jul 23, 2019 at 08:19 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 23, 2019 at 08:19 PM
The last column is a numeric value that represents a payment.
Ok, this will scrub across 26 columns. Set the variable of Z if you want a different number of columns.
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Z = 26 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet2","Sheet4"}] ' place your sheet names here
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
-For tabcounter = 1 To thetab
--------For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
--------------For columnCount = 1 To Z
-------------------thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
-------------------ThisWorkbook.Worksheets(1).Cells(findlastrow(1) + 1, columnCount).Value = thevalue
--------------Next
--------Next
-Next
End Sub
I really hope you are learning how to construct a solution!
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Z = 26 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet2","Sheet4"}] ' place your sheet names here
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
-For tabcounter = 1 To thetab
--------For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
--------------For columnCount = 1 To Z
-------------------thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
-------------------ThisWorkbook.Worksheets(1).Cells(findlastrow(1) + 1, columnCount).Value = thevalue
--------------Next
--------Next
-Next
End Sub
I really hope you are learning how to construct a solution!
ThanatosTalos
Jul 23, 2019 at 08:20 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 23, 2019 at 08:20 PM
Hello Mark, yes, i am trying to learn as much as possible. Sorry for the delay between responses, i was commuting back from work, i will test this new solution as soon as possible
ThanatosTalos
Jul 24, 2019 at 11:36 AM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 11:36 AM
Hello Mark, i have been trying out the code, but the second line of code
"findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row"
gives out error 9, saying that it is outside the interval, and i have yet to find a way to fix it
"findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row"
gives out error 9, saying that it is outside the interval, and i have yet to find a way to fix it
Ok well that is a function and it returns the last row of what ever sheet you paas in the parameter of whatsheet. Are you passing a sheet name? That function will error out if ran alone, as ot needs to passed a variable. In rhis case the variabke is the sheet that you want counted.
This ia the complete function, below.
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
This ia the complete function, below.
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
ThanatosTalos
Jul 24, 2019 at 12:22 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 12:22 PM
It should be Sheet4, and i sustitute "whatsheet" for Sheet4, and it still doesn't run. The only way to get pass it that i find, was to include the name of all the sheets that participate in the process, but that seems incorrect too, as it tells me the "Sub" is not optional anymore
ThanatosTalos
Jul 24, 2019 at 12:37 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 12:37 PM
Function findlastrow(Sheet4)
findlastrow = ThisWorkbook.Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
End Function
Is this not correct for what i want to do?
findlastrow = ThisWorkbook.Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
End Function
Is this not correct for what i want to do?
Ok, jist remove the sheet4 from the parameter. So the first line should read,
Function findlastrow ()
That should do it. BUT, IT WILL CAUSE THE MAIN SUBROUTINE TO FAIL, AS IT PASSES THE SHEET INTO THAT FUNCTION. SO, in prder to avoid changing the code, just change the sheets listed in the variable of sheetsList ().
So, sheetsList=[{"Sheet4"}]
So, please do not make this more complicated than it has to be.
Function findlastrow ()
That should do it. BUT, IT WILL CAUSE THE MAIN SUBROUTINE TO FAIL, AS IT PASSES THE SHEET INTO THAT FUNCTION. SO, in prder to avoid changing the code, just change the sheets listed in the variable of sheetsList ().
So, sheetsList=[{"Sheet4"}]
So, please do not make this more complicated than it has to be.
ThanatosTalos
Jul 24, 2019 at 01:04 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 01:04 PM
Function findlastrow()
findlastrow = ThisWorkbook.Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet4"}] ' place your sheet names here
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets(1).Cells(findlastrow(1) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
This is what i have, and it still wont' run. I am sorry, I am starting to think you are being annoyed by this, but if you think enough's enough, we can close the topic
findlastrow = ThisWorkbook.Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet4"}] ' place your sheet names here
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets(1).Cells(findlastrow(1) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
This is what i have, and it still wont' run. I am sorry, I am starting to think you are being annoyed by this, but if you think enough's enough, we can close the topic
ThanatosTalos
Jul 24, 2019 at 01:12 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 01:12 PM
Pardon me, but i thought the array's job was to tell the code what sheets it needed to go through to copy and combine in the "master sheet". Now we are using it to tell the code that the sheet that it needs to read and find last row of it's sheet 4, no?
No. You are correct with the first statement. If you wish to change the sheet it writes to, that is a different solution, and I have not addressed the configuration of that sheet.
In the bottom of the code at this line,
ThisWorkbook.Worksheets(1).Cells(findlastrow(1) + 1, columnCount).Value = thevalue
Change
thisworkbook.worksheets (1) to
Thisworkbook.worksheets ("sheet4").cells (findlastrow ("sheet4")+1, columnCount.Value = thevalue
In the bottom of the code at this line,
ThisWorkbook.Worksheets(1).Cells(findlastrow(1) + 1, columnCount).Value = thevalue
Change
thisworkbook.worksheets (1) to
Thisworkbook.worksheets ("sheet4").cells (findlastrow ("sheet4")+1, columnCount.Value = thevalue
ThanatosTalos
Updated on Jul 24, 2019 at 01:42 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Updated on Jul 24, 2019 at 01:42 PM
Function findlastrow()
findlastrow = ThisWorkbook.Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet4"}] ' place your sheet names here
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets("sheet4").Cells(findlastrow(1) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
This is what i have, but i still have the error 9
findlastrow = ThisWorkbook.Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet4"}] ' place your sheet names here
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets("sheet4").Cells(findlastrow(1) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
This is what i have, but i still have the error 9
Didn't find the answer you are looking for?
Ask a question
You did not change the last line correctly. It should read as
Thisworkbook.worksheets ("sheet4").cells (findlastrow ("sheet4")+1, columnCount.Value = thevalue
I highlighted and underlined the part in your code that is incorrect. Just use the below code in its entirety.
Thisworkbook.worksheets ("sheet4").cells (findlastrow ("sheet4")+1, columnCount.Value = thevalue
I highlighted and underlined the part in your code that is incorrect. Just use the below code in its entirety.
ThanatosTalos
Jul 24, 2019 at 01:44 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 01:44 PM
i see, i missed changing the second 1 on the line
Complete code with new placesheet variable
The sheets you wish to have scrubbed and moved should be listed in the sheetList array. The sheet that you wish to copy the values to, is set by placesheet. Z is the number of columns across you wish to copy.
The sheets you wish to have scrubbed and moved should be listed in the sheetList array. The sheet that you wish to copy the values to, is set by placesheet. Z is the number of columns across you wish to copy.
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'It will loop through each sheet and place the contents onto the sheet set by placesheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Dim placesheet
placesheet = "Sheet4" 'this is the sheet you wish to move them to
Z = 26 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet6","Sheet7"}] ' place your sheet names here
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets(placesheet).Cells(findlastrow(placesheet) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
ThanatosTalos
Jul 24, 2019 at 01:46 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 01:46 PM
Excuse me, and I understand this may be getting annoying, but in the fuction;
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Then we are going back to before, so should it look like this:
Function findlastrow()
findlastrow = ThisWorkbook.Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
End Function
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Then we are going back to before, so should it look like this:
Function findlastrow()
findlastrow = ThisWorkbook.Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
End Function
ThanatosTalos
Jul 24, 2019 at 02:07 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 02:07 PM
Function findlastrow()
findlastrow = ThisWorkbook.Worksheets().Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Dim placesheet
placesheet = "Sheet4" 'this is the sheet you wish to move them to
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet3","Sheet5","Sheet6","Sheet7"}]
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets(placesheet).Cells(findlastrow(placesheet) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
This is what i Have now, and I am trying to figure out what "whatsheet" should be substitute by
findlastrow = ThisWorkbook.Worksheets().Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Dim placesheet
placesheet = "Sheet4" 'this is the sheet you wish to move them to
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet3","Sheet5","Sheet6","Sheet7"}]
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets(placesheet).Cells(findlastrow(placesheet) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
This is what i Have now, and I am trying to figure out what "whatsheet" should be substitute by
Do you see the line
Because we use this in our function, whatsheet has to remain whatsheet! Just use the original function, posted again for clarity.
This should be it. After you get this to work, we will expand on it, and make it do some extras stuff as you identify what else you need!
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
Because we use this in our function, whatsheet has to remain whatsheet! Just use the original function, posted again for clarity.
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
This should be it. After you get this to work, we will expand on it, and make it do some extras stuff as you identify what else you need!
ThanatosTalos
Jul 24, 2019 at 02:46 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 02:46 PM
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Dim placesheet
placesheet = "Sheet4" 'this is the sheet you wish to move them to
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet3","Sheet5","Sheet6","Sheet7"}]
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets(placesheet).Cells(findlastrow(placesheet) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
This si what i have now; i have listed the sheets i want to be copied to sheet 4, and the number of columns i want, however, it still wont run because of error 9. I feel like i am missing something dumb
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub CombineSheets()
'this code assumes your master sheet is on the first sheet
'It will loop through each sheet and place the contents onto the first sheet
Dim tabcount
Dim rowcounter
Dim thevalue
Dim Z
Dim sheetsList()
Dim placesheet
placesheet = "Sheet4" 'this is the sheet you wish to move them to
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet3","Sheet5","Sheet6","Sheet7"}]
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets(placesheet).Cells(findlastrow(placesheet) + 1, columnCount).Value = thevalue
Next
Next
Next
End Sub
This si what i have now; i have listed the sheets i want to be copied to sheet 4, and the number of columns i want, however, it still wont run because of error 9. I feel like i am missing something dumb
ThanatosTalos
Jul 24, 2019 at 03:15 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 03:15 PM
"Microsoft Scripting Runtime" was not checked, and i checked. Still nothing.
Also my object libraries version is 15.0; could that be the problem?
Also my object libraries version is 15.0; could that be the problem?
I dont think so, as the methods I am using is nothing new!
Do this. Remove that module from the workbook. Add in another blank module. Cut the code from your post above. And paste it into the new module. Try it again.
If it errors out, hit debug and take a screen shot of the yellow highlighted line that it is complaining about. Its good, we will work it.
Do this. Remove that module from the workbook. Add in another blank module. Cut the code from your post above. And paste it into the new module. Try it again.
If it errors out, hit debug and take a screen shot of the yellow highlighted line that it is complaining about. Its good, we will work it.
ThanatosTalos
Jul 24, 2019 at 03:28 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 03:28 PM
ThanatosTalos
Jul 24, 2019 at 03:35 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 03:35 PM
it will not let me
ThanatosTalos
Jul 24, 2019 at 03:35 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 03:35 PM
it will not let me upload a screenshot at all
ThanatosTalos
Jul 24, 2019 at 03:36 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 03:36 PM
nevermind, it worked all of the sudden
ThanatosTalos
Jul 24, 2019 at 03:39 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 03:39 PM
it still errors, but i am unclear in what i am suppose to do so it may be an error on my end, all i am doing is placing the cursor and that line and the hitting F5
ThanatosTalos
Jul 24, 2019 at 03:50 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 03:50 PM
it thinks it's sheet 3, it says whatsheet = Sheet3
ThanatosTalos
Jul 24, 2019 at 03:59 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 03:59 PM
There is a Sheet 3, not a sheet called "Sheet 3"; but there is definetely one assigned to that value; in fact it is one of the specific one the array checks
ThanatosTalos
Jul 24, 2019 at 04:08 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 04:08 PM
Excuse me, what do you mean precisely by "the names of the tabs have to match the names on the list"? english is not my first language, and for me it seems to mean that the name on the list in the vba screen has to be the same as the tabs in the excel and in the code
ThanatosTalos
Jul 24, 2019 at 04:05 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 24, 2019 at 04:05 PM
OH! Hold on, there is a sheet called "Sheet 3", it's sheet 9, i just forgot about it, because it is hidden. I use it to create a dependant dropt list somwhere else
OK, I scaled out the previous posted code, and it was not presenting a large dataset correctly. I have posted a correction to the code. This is not going to fix a runtime 9 error!
Run the below code, it calls the one above!
Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function
Run the below code, it calls the one above!
Sub CombineSheets()
'Start here. This code will loop through
'each sheet in the sheetsList() array,
'and places the contents onto the sheet identified in placesheet
'!The list names in sheetList() must Match the Sheet names!
Dim tabcount, rowcounter, Z
Dim thevalue
Dim sheetsList()
Dim placesheet, placesheetrow
placesheet = "Sheet1" 'this is the sheet you wish to move them to
Z = 15 ' this is the number of columns to copy to the new sheet!
sheetsList() = [{"Sheet3","Sheet4","Sheet2"}] 'this is the sheet list
thetab = UBound(sheetsList) - LBound(sheetsList) + 1
For tabcounter = 1 To thetab
For rowcounter = 1 To findlastrow(sheetsList(tabcounter))
placesheetrow = findlastrow(placesheet) + 1
For columnCount = 1 To Z
thevalue = ThisWorkbook.Worksheets(sheetsList(tabcounter)).Cells(rowcounter, columnCount).Value
ThisWorkbook.Worksheets(placesheet).Cells(placesheetrow, columnCount).Value = thevalue
Next
Next
Next
End Sub
Did I lose ya?
ThanatosTalos
Jul 28, 2019 at 03:49 PM
- Posts
- 30
- Registration date
- Monday July 22, 2019
- Status
- Member
- Last seen
- July 28, 2019
Jul 28, 2019 at 03:49 PM
Hello Mark, I have been extremely busy with work and commuting between my home town and the big city; however I must thank you so much. Because after many tries and thanks to your help I managed to create the sheet as intended, in fact, i turned in the work yesterday, reason why I have been so busy and i have not been able to reply to your posts.
Many thanks, because without you, I wouldn't manage to complete the work in time, and as an extra, I learn quite a lot. Good riddance my friend, may we see each other again.
Many thanks, because without you, I wouldn't manage to complete the work in time, and as an extra, I learn quite a lot. Good riddance my friend, may we see each other again.
Jul 23, 2019 at 02:14 PM
Jul 23, 2019 at 02:28 PM
Do you know how to build a " for loop " in VBA?