Questions, drop down, store answers

[JoeK210185]

Hello all.

I hope I can explain it properly what I'm trying to achieve.

I basically have 10 or so advisors who will all have their own spreadsheet, all with the same questions on each one.

What I need to know is, how do I have a master copy in the background that will capture every set of answers the advisor puts in.

For example Advisor 1 pick the answers...

Q1 Q2 Q3
orange large carton sumbit


Now when the advisor puts the answers in an presses 'submit' I want the answer to go to a 'master' sheet with all the other entries?

Is it possible as I'm not sure. Thanks.

 

[Trevor G]

If you are using a command button then you should be able to achieve this.

Say from Sheet1 to Sheet2. Use Alt + F11 Then Insert Menu and Module, Copy this code. Then add a button to the sheet. You then can adapt this for each Advisor's sheet, you would need to change the Sub name to have a different button on each sheet and also the sheet names.

Try this out.

Sub CopyToSheet2()
Application.ScreenUpdating = False
ActiveCell.EntireRow.Copy
Sheets("Sheet2").Activate
Range("a1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.EntireRow.PasteSpecial xlPasteAll
Sheets("Sheet1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[JoeK210185]

Hello Trevor.

Is there a way I can get this to copy to a 'master' file.

So I give all 10 people a copy of the spreadsheet with the only difference being their ID and then all the 'sumbitted entries' going on to one, seperate, master tab?

Thanks.

 

[Trevor G]

You would need to add a workbook command then.

workbooks.Open("C:\Path\Master.xls").Sheets("Master").Activate

 

[JoeK210185]

Do I just add that to the very bottom of the command?

 

[Trevor G]

It would have to be as part of the loop.

But this would mean that every time they do an new entry you are opening the workbook, so the Master Workbook would have to be set up as a shared workbook.

Think about this as an Idea.

Run with the code I have given you as it is, but then before the workbook is closed take the data in one goe into the Master workbook, otherwise you might get conflicts if it is already open.

To do this you would use the workbook event BeforeClose then the code would need to be added to select the data in sheet(2) and copy it across then delete the data in sheet(2).

 

[JoeK210185]

Nice one Trevor, you've been a brilliant help.

I've got one last question, Is it possible for me, once the advisor has clicked the 'submit' button, for the cells to be cleared, except A1 and B1?

 

[Trevor G]

Add these lines

Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).ClearContents

 

[JoeK210185]

Sorry mate I meant keep the info that is in A1 and B1 as that is information that will not need editing or re-entering every time?

 

[Trevor G]

That code will keep Row1 data