Auto-populate a sheet with data from different other sheets [Solved/Closed]

Question

Auto-populate a sheet with data from different other sheets [Solved/Closed]

Report

ThanatosTalos

Posts: 30
Registration date: Monday July 22, 2019
Status: Member
Last seen: July 28, 2019

- Jul 22, 2019 at 03:31 PM
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.

System Configuration: Windows / Chrome 75.0.3770.142

How to auto populate data from multiple sheets to a master
How to auto populate data from master to multiple sheets - Best answers
Populate Multiple Sheets Based on Data Entered in a Main Excel Sheet - How-To - Excel
Auto-populate info. from master sheet to multiple sheets ✓ - Forum - Excel
Auto populate rows from master sheet to other sheets Excel 2013 ✓ - Forum - Excel
Autopopulating multiple sheets from one main sheet ✓ - Forum - Excel
Auto Update Master Sheet - Forum - Excel

9 replies

Ask a question

Blocked Profile · Answer 1 · 2019-07-22T16:35:18-0400

Answer 1 / 9

Blocked Profile
Jul 22, 2019 at 04:35 PM

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!

Report

ThanatosTalos

Posts: 30
Registration date: Monday July 22, 2019
Status: Member
Last seen: July 28, 2019

Jul 23, 2019 at 02:14 PM

Hold on, wouldn't this mean then i would have to manually input every single cell reference to the sheet? That would be cumbersome

Report

Blocked Profile
Jul 23, 2019 at 02:28 PM

Yes, and no its not with an auto drag.

Do you know how to build a " for loop " in VBA?

Blocked Profile · Answer 2 · 2019-07-23T14:45:28-0400

Answer 2 / 9

Blocked Profile
Jul 23, 2019 at 02:45 PM

Here is code that will do it...

View all 6 comments

Report

ThanatosTalos

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?

Report

Blocked Profile
Jul 23, 2019 at 03:23 PM

This code counts the nunber of aheets. If it finds five sheets, it will combine sheet2,3,4,5 onto sheet1. You place the code in a module, from the developers tab. Then press run.

Report

ThanatosTalos

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

Report

Blocked Profile
Updated on Jul 23, 2019 at 04:38 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 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

Report

ThanatosTalos

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

Blocked Profile · Answer 3 · 2019-07-23T16:50:23-0400

Answer 3 / 9

Blocked Profile
Updated on Jul 23, 2019 at 06:16 PM

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:


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?

Report

ThanatosTalos

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

Report

Blocked Profile
Updated on Jul 23, 2019 at 05:01 PM

What is the last column?

Report

ThanatosTalos

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.

Blocked Profile · Answer 4 · 2019-07-23T18:50:33-0400

Answer 4 / 9

Blocked Profile
Updated on Jul 23, 2019 at 08:43 PM

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!

View all 12 comments

Report

Blocked Profile
Jul 24, 2019 at 01:07 PM

Ok, put the original function back the way I published it. It should run, as you are limiting the sheet in the sheetList array variable. You are so close!

Report

ThanatosTalos

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?

Report

Blocked Profile
Jul 24, 2019 at 01:22 PM

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

Report

ThanatosTalos

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

Report

Blocked Profile
Jul 24, 2019 at 01:37 PM

The function is not as original.

Use this one

Function findlastrow(whatsheet)
findlastrow = ThisWorkbook.Worksheets(whatsheet).Cells(Rows.Count, 1).End(xlUp).Row
End Function

Blocked Profile · Answer 5 · 2019-07-24T13:38:33-0400

Answer 5 / 9

Blocked Profile
Updated on Jul 24, 2019 at 01:43 PM

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.

Report

ThanatosTalos

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

Blocked Profile · Answer 6 · 2019-07-24T13:41:03-0400

Answer 6 / 9

Blocked Profile
Updated on Jul 24, 2019 at 01:48 PM

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.


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

View all 24 comments

Report

ThanatosTalos

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

Report

Blocked Profile
Jul 24, 2019 at 04:03 PM

The names on the tabs have to match the names on the list.

Report

ThanatosTalos

Posts: 30
Registration date: Monday July 22, 2019
Status: Member
Last seen: July 28, 2019

> Blocked Profile
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

Report

ThanatosTalos

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

Report

Blocked Profile
Updated on Jul 24, 2019 at 04:16 PM

If sheet number 1 is named REPORTS, then you must reference It by REPORTS, not sheet1.

Blocked Profile · Answer 7 · 2019-07-24T18:34:01-0400

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!

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

Blocked Profile · Answer 8 · 2019-07-25T07:48:47-0400

Answer 8 / 9

Blocked Profile
Jul 25, 2019 at 07:48 AM

Ok, here is a little more detail.

Blocked Profile · Answer 9 · 2019-07-26T10:55:14-0400

Answer 9 / 9

Blocked Profile
Jul 26, 2019 at 10:55 AM

Did I lose ya?

Report

ThanatosTalos

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.

Report

Blocked Profile
Jul 29, 2019 at 07:07 AM

Thank you. I must say, good riddance is not a closure that English speakers use when saying you wish to see someone again. As a matter of fact, ypu say that when you wish to No Longer see them again.

Come back when you need more help! I am glad you have learned something.

Auto-populate a sheet with data from different other sheets [Solved/Closed]

9 replies

Your reply

Subscribe To Our Newsletter!