Excel Macro that Pulls Data from Dynamic Sheets

ricksmith324

New Member
Joined
Mar 21, 2017
Messages
4
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top