Excel Macro that Pulls Data from Dynamic Sheets

[ricksmith324]

Hi All,

I'm new to the thread and am looking for some help with a macro that I'm building. I'm an above average excel user and am exploring some options with macros. I have a "Summary" tab that essentially has employee data and will be rolling up details at a summary level using various formulas and pulling from separate employee tabs. My spreadsheet currently has a macro that allows users to "Add a New Employee". This macro creates a separate tab from the summary tab and will be used to capture employee metrics. Once the user goes into the newly created tab (after clicking on Add a New Employee), they can type in the employees name and the tab will automatically rename to that employees name.

The problem I'm having is developing a macro so that each time a new employee is added, a row will be inserted into the Summary tab with that employees information. The formulas on each row in the summary tab are various vlookups off of the employee tabs (i.e. vlookup(C2,'TAB NAME',$A$1:$B$500,5,FALSE). The TAB NAME will represent each new employee that is added. I don't know how to have the macro pull in data from a new sheet each time a new employee is added. Below is the macro that I have built. Essentially the name "Rick Smith" should represent the new employee. I dont know if I have to do something with Active.Sheet or something along those lines, but any help would be greatly appreciated!

Sub Test()
'
' Test Macro
'


'
Sheets("Department Summary").Select
Rows("4:4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A4").Select
ActiveCell.FormulaR1C1 = "='Rick Smith'!R[-3]C[1]"
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Rick Smith'!R[-2]C"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=MAX('Rick Smith'!R5C2:R151C2)"
Range("D4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],'Rick Smith'!R[1]C[-2]:R[147]C[2],5,0),"""")"
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-2],'Rick Smith'!R[1]C[-3]:R[147]C[4],6,FALSE),"""")"
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-2],'Rick Smith'!R5C2:R151C9,6,FALSE),"""")"
Range("D4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],'Rick Smith'!R5C2:R151C6,5,0),"""")"
Range("F4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],'Rick Smith'!R[1]C[-4]:R[147]C[7],9,0),"""")"
Range("F4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],'Rick Smith'!R[1]C[-4]:R[147]C[7],8,0),"""")"
Range("F4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],'Rick Smith'!R5C2:R151C13,8,0),"""")"
Range("G4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-4],'Rick Smith'!R[1]C[-5]:R[147]C[6],10,0),"""")"
Range("G4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-4],'Rick Smith'!R[1]C[-5]:R[147]C[6],9,0),"""")"
Range("G4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-4],'Rick Smith'!R5C2:R151C13,9,0),"""")"
Range("H4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],'Rick Smith'!R[1]C[-6]:R[147]C[6],10,0),"""")"
Range("H4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],'Rick Smith'!R5C2:R151C14,10,0),"""")"
Range("I4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-6],'Rick Smith'!R[1]C[-7]:R[147]C[4],0),"""")"
Range("I4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-6],'Rick Smith'!R[1]C[-7]:R[147]C[4],12,0),"""")"
Range("I4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-6],'Rick Smith'!R5C2:R151C13,12,0),"""")"
Range("I5").Select
End Sub