Referencing a newly created worksheet

[Charyn]

Good evening NG!

I am currently studying accounting and have taken a class that expands our
(students) knowledge of excel, macros, etc. as they apply in the world of
accounting. Our term project was to create a payroll workbook in excel that
used VBA, Macros and several other excel features to assist in the
weekly/yearly chores of processing payroll. I completed the project within
the stated parameters, but in the process I have become totally intrigued
with VBA (enough to take a summer course for non-programmers) and know that
there are things that can be enhanced beyond what we were expected to
accomplish.

OK - enough background - here's the puzzle I am trying to unravel:

The workbook I created taken user inputs to create employee records on a
roster. I then have a macro that I enhanced the VBA code for that tranfers
all of the new employee data onto a payroll register for weekly payroll and
an annual record specific to that individual. All of the taxes are
calculated through formulas gor deduction.

The problem I am having is in getting the formula for social security to
automtically populate on the weekly register. As SS is capped - the total
paid (on the annual record) is relevant to the formula to calculate the
amount of tax. I can hand enter the formula - pointing to the YTD gross
pay, but I would like to abe able to have that formula generated
automatically when the new employee is added to the payroll register and
their annual record is created.

Sorry for the long post - and Thanks for any direction you can point me

Charyn

 

[Vasant Nanavati]

If you post some relevant excerpts of your code and some more specifics, it
owuld be easier to give you a good answer.

 

[Charyn]

Here's some more information - being new to this I am not sure what you
would need so I posted what I have done to create the new employee and the
formula I have genereated for SS that needs to reference a newly created
worksheet. Thank you for taking the time and having the patience )

Formula used to calculate SS amount to deduct:

=IF('Employee Annual Record -
Cucumb'!F81>=Social_Security_Cap,0,IF('Employee Annual Record -
Cucumb'!F81+'Weekly Payroll
Register'!I6>=Social_Security_Cap,(Social_Security_Cap-'Employee Annual
Record - Cucumb'!F81)*Social_Security_Rate,'Weekly Payroll
Register'!I6*Social_Security_Rate))

" 'Employee Annual Record - Cucumb'!F81 " is the cell containing the YTD
gross pay on the employee's annual record. Each employee has a separate
worksheet for their annual record and they are named according to the first
six letters of their name - Amada Apple's would be " 'Employee Annual
Record - Apple'!F81 "

Of course, by reading this NG and reading more instructional material
contained on some(many) of the websites posted here I am sure I will find
several other ways to do things more efficiently and/or elegantly - and most
seems as if I play with it a bit I'll get the hang of it.....but this
formula one I dont even know where to begin

The code to create the new employee is as follows (long):

Sub New_Employee()
'
' New_Employee Macro
' Macro recorded 3/13/2005 by Varkonyi-Compeau
'
Dim ShtName As String 'sets variable for the sheet name

'
Application.Goto Reference:="Employee_Number"
Do Until ActiveCell = Blank
ActiveCell.Offset(1, 0).Range("A1").Select
Loop 'Do until loop forces excel to continue process until value is
found, offset determines where excell will look next
ActiveSheet.Unprotect
ActiveCell.FormulaR1C1 = InputBox("Enter Next Available Employee
Number - refer to instructions for guidance on employee number selection",
"Employee #", "101")
ShtName = "Employee Annual Record -" & InputBox("Please enter the
Employee's short name - Typically the first six letters of their last
name.", "Employee Name")
'ShtName = establishes the value that will be assigned to the variable
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee Name: First Name,
Last Name", "Employee Name", "John Doe")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee Address", "Employee
Address", "123 Any Street")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee City", "Employee
City", "Your Town")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee State", "Employee
State", "Ohio")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee Zip Code", "Employee
Zip Code", "99999")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee Social Security
Number", "Employee Social Security Number", "123-45-6789")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee Marital Status -
refer to the employee's W-4 form. Enter 'S' for Single, 'M' for Married",
"Employee Federal Marital Status", "S")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee Federal Exemptions -
Refer to the employee's W-4 form", "Employee Federal Exemptions", "1")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee Pay Rate", "Employee
Pay Rate", "5.75")
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Employee Status: 'A' for
active, 'I' for inactive", "Employee Pay Rate", "A")
Application.Goto Reference:="Employee_Number"
Do Until ActiveCell = Blank
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Hidden = MsgBox("Please review your entries and click 'save' before
continuing.", vbOKOnly, "Save Employee")
Sheets("Employee Annual Record Blank ").Select
Sheets("Employee Annual Record Blank ").Copy Before:=Sheets(4) 'This is
the process that create a new sheet from an established template
Sheets("Employee Annual Record Blan (2)").Select
Sheets("Employee Annual Record Blan (2)").Name = ShtName 'This is the
process that renames the sheet
Application.Goto Reference:="Employee_Number"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Do Until ActiveCell = Blank
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

End Sub

Remember - be gentle - this is my very fist baby ;-)

Thank again - Charyn