Auto Populate from dashboard to second spreadsheet

[Joeclupis]

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]

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]

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]

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]

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]

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]

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]

ok I'm writing something up, give me 10 minutes...

 

[Roderick_E]

sorry taking longer than I thought, trying to future proof it

 

[Roderick_E]

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

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