Auto Populate from dashboard to second spreadsheet

Joeclupis

Board Regular
Joined
Jun 18, 2016
Messages
63
I have a workbook with multiple sheets. I would like to learn how to set up my dashboard so that I can enter data on it, then have the data fill in the first open row on the spreadsheet. The spreadsheet has 17 columns of data with 2 blank columns after the 5th and 9th columns of information. We have over 3900 rows filled in now. I have googled for some help, but the code is above my head. I understand how to have a spreadsheet look at another for data, but not how to use one row on a spreadsheet to fill in a row on a different spreadsheet, and knowing that the row on the second spreadsheet will always change to the next empty row.

Joseph Carney
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
When you use the term "dashboard" what do you mean. Is this a worksheet you are using as a master sheet or is it a form?
 

Joeclupis

Board Regular
Joined
Jun 18, 2016
Messages
63
It is the first sheet in the workbook. It displays graphs from the other pages in the workbook. We would like to NOT have to enter data on multiple sheets that are 'behind' the dashboard. I have been trying to consolidate all the spreadsheets requiring data entry into one large spreadsheet and then using that sheet to drive the graphs. I have looked at using a userform, but cannot get it to open on the main page but look to the data page for information. I decided to see if I could get around the issue by trying to enter the data on the main "dashboard" page.

I hope that this explains it.
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Hmm, Ok, adding new lines isn't so much and issue, but it you want to modify or delete that will be a bigger project. Are the cells on all the receptor sheets the same cells on the master sheet. So for example, A14 on master and A14 on all receptor sheets or do we need to define the trigger columns?
 

Joeclupis

Board Regular
Joined
Jun 18, 2016
Messages
63
The main sheet has no columns set up as of yet. it is just graphs. And we do NOT want to delete or modify what we have, just add to the next empty row. If I could, I'd upload a screenshot of what I have. I think I understand what you are asking reference trigger columns. The main thing is that we enter data in the main sheet row 2, the data sheet gets populated in the next available row, and the main sheet then can clear the entry, waiting on the next entry.
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
ahhh...would you be opposed to a "COPY" button? So you would input you data in the master sheet, then press the copy button and it would copy all the data into the various sheets on their next blank row? Also, have you thought about how to prevent duplicates? Is there some unique reference column I could use to check if the user already input that data?
 

Joeclupis

Board Regular
Joined
Jun 18, 2016
Messages
63
If it works, then it would be fine. I believe in work smarter, not harder. Although, telling the boss that I could do it was not such a good idea!
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
sorry taking longer than I thought, trying to future proof it
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Ok this will be basic at first... To test create a new workbook with at least 2 sheets; Sheet1 and Sheet2

Directions:
1) On Sheet2 put a header in cells D1, E1, F1 (can be the words test1, test2, test3)
2) Now we need to get to the VBA editor. Press ALT+F11
3) Insert and MODULE
4) Paste in my code from my code box below.
5) Add a button to sheet1 (do so by enabling Developer tab, File/Options/Custom Ribbon, select Developer
6) You should see new menu option at top called Developer
7) Click on Developer menu, insert Form Button
8) Right click button select the copymasterdata macro to assign to button
9) On sheet 1 type some text into cells A3 B3 and C3
10) Press button, see text now on Sheet2
11) Press button again, a new row shows on Sheet2

Code:
Option Compare Text 'ignore text case
Sub copymasterdata()
Dim mastersh As Worksheet
Dim receptorsh1 As Worksheet
Dim lastrow1 As Long
Dim masterdatacells As String
Dim recept1cols As String
Dim masterunbound As Variant
Dim recept1unbound As Variant
Dim receptstr As String
Dim maststr As String
Dim i As Long


Set mastersh = Worksheets("Sheet1") 'set masterworksheet, change "Sheet1" to worksheet's actual name
Set receptorsh1 = Worksheets("Sheet2") 'set receptor sheet, change "Sheet2" to worksheet's actual name
masterdatacells = "A3, B3, C3" 'these are the cells on the master sheet that will be copied
recept1cols = "D, E, F" 'these the the columns to copy to on the receptor sheet


'unfilter receptor sheet if filtered -- make for each receptor sheet next 3 lines, change numbers
If receptorsh1.AutoFilterMode Then
receptorsh1.Cells.AutoFilter
End If


'determine last row of each receptorsheet -- make for each receptor sheet change numbers
lastrow1 = receptorsh1.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
masterunbound = Split(masterdatacells, ",")
recept1unbound = Split(recept1cols, ",")
For i = LBound(masterunbound) To UBound(masterunbound)
receptstr = Trim(recept1unbound(i))
maststr = Trim(masterunbound(i))
receptorsh1.Cells(lastrow1, receptstr) = mastersh.Range(maststr)
Next i






MsgBox "Copied", vbInformation, "CONFIRMATION"


End Sub
 
Last edited:

Forum statistics

Threads
1,081,860
Messages
5,361,734
Members
400,652
Latest member
cortexnotion

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top