Hello everyone,
I created a Payroll Workbook and was just about to complete it when I realized I wanted to include a Corrections worksheet because I don't want the user to go into sensitive worksheets and mess with data they shouldn't be accessing.
So here's my scenario:
In my Corrections worksheet;
A5:F5 - Formulas to retrieve existing data from any of 12 sheets with two criteria (Name & Date)
A6:F6 - Cells to replace the existing data (User Entry)
A7:F7 - Formula to retrieve and display cell address for each value in A5:F5 using two criteria (Name & Date). These cells, A7:F7, are hidden by Format ";;;"
I started to create a module to Update the data by declaring variables for each value of new data (A6:F6) and each of the cell addresses (A7:F7). I don't know how to get the new data into the cell using the cell address variable.
Being such a Noob, I tried to figure it out via VBA and wiped out some formulas on the Corrections worksheet and had to start over. Thank goodness for back-ups.
I created a stripped down version of the workbook to include here but XL2BB won't work because the formulas have arrays in them so I have it here on Google Drive Google Drive file location if you want to take a look at it.
Any help would be an education on my part and much appreciated. Thanks!
Derick
I created a Payroll Workbook and was just about to complete it when I realized I wanted to include a Corrections worksheet because I don't want the user to go into sensitive worksheets and mess with data they shouldn't be accessing.
So here's my scenario:
In my Corrections worksheet;
A5:F5 - Formulas to retrieve existing data from any of 12 sheets with two criteria (Name & Date)
A6:F6 - Cells to replace the existing data (User Entry)
A7:F7 - Formula to retrieve and display cell address for each value in A5:F5 using two criteria (Name & Date). These cells, A7:F7, are hidden by Format ";;;"
I started to create a module to Update the data by declaring variables for each value of new data (A6:F6) and each of the cell addresses (A7:F7). I don't know how to get the new data into the cell using the cell address variable.
VBA Code:
Sub Update()
' The GoTO***** Variables below are taken from Worksheet Corrections Range A7:F7 where a formula
' exists to retrieve the cell address from which the data in A5:F5 resides.
' Worksheet Corrections A6:F6 is the data to replace A5:F5
Dim HrsWkd, HlyDy, BnsCom, Loan, VacBal, SickBal As Integer
Dim GoToHrsWkd, GoToHlyDy, GoToBnsCom, GoToLoan, GoToVacBal, GoToSickBal As String
HrsWkd = Sheets("Corrections").Range("A6").Value 'New Hours
GoToHrsWkd = Sheets("Corrections").Range("A7") 'Where New Hours will be placed
HlyDy = Sheets("Corrections").Range("B6").Value 'New Holiday Hours
GoToHlyDy = Sheets("Corrections").Range("B7") 'Where New Holiday Hours will be placed
BnsCom = Sheets("Corrections").Range("C6").Value 'New Bonus/Commissions
GoToBnsCom = Sheets("Corrections").Range("C7") 'Where New Bonus/Commissions will be placed
Loan = Sheets("Corrections").Range("D6").Value 'New Loan Balance
GoToLoan = Sheets("Corrections").Range("D7") 'Where New Loan Balance will be placed
VacBal = Sheets("Corrections").Range("E6").Value 'New Vacation Days Balance
GoToVacBal = Sheets("Corrections").Range("E7") 'Where New Vacation Days Balance will be placed
SickBal = Sheets("Corrections").Range("F6").Value 'New Sick Days Balance
GoToSickBal = Sheets("Corrections").Range("F7") 'Where New Sick Days Balance will be placed
End Sub
Being such a Noob, I tried to figure it out via VBA and wiped out some formulas on the Corrections worksheet and had to start over. Thank goodness for back-ups.
I created a stripped down version of the workbook to include here but XL2BB won't work because the formulas have arrays in them so I have it here on Google Drive Google Drive file location if you want to take a look at it.
Any help would be an education on my part and much appreciated. Thanks!
Derick