This macro will only complete the form OSHA Form 300. You have to select an establishment first before the macro will run. If you fail to do that it gives you a message telling you to chose one until you do. Sorry for the length. I am sure there is a way to shorten that repeatative part. I would love to know how if someone else wants to rewrite that part. Let me know if you want this macro to be modified or create a new macro to fill out the other forms. I will need an example of what you want to see for the other forms if so. Thanks.
Sub AutoFillOHSAForms()
'CHECK FEATURE TO MAKE SURE AN ESTABLISHMENT IS SELECTED FIRST
If Sheets("OSHA Form 300").Range("K11").Value = "" Then
MsgBox "***Please select an Establishment on OSHA Form 300 Cell K11 before running this macro."
GoTo ENDMACRO:
End If
'CLEAN OSHA FROM 300
Sheets("OSHA Form 300").Range("A25:R128").ClearContents
Sheets("OSHA Form 300").Range("B25").Select
'===============================================================================================
'SETUP WORKSHEET FOR MACRO, FORMAT, FIND LAST ROW WITH CONSTANT, THEN BEGIN COPING SEQUENCE
'===============================================================================================
'CHANGE THE ADDRESSES FACILITY TO UPPCASE TO MATCH THE DATA FILE FORMAT
Sheets("Addresses").Select
Range("H2").Select
Do Until ActiveCell.Offset(0, -7).Value = ""
ActiveCell.FormulaR1C1 = "=UPPER(RC[-7])"
ActiveCell.Offset(1, 0).Select
Loop
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Copy
Range("A2").PasteSpecial Paste:=xlPasteValues
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
'FIND LAST ROW # OF DATA FILE
Sheets("Data File").Select
n1 = Range("A" & Rows.Count).End(xlUp).Row
'SETUP i FOR # OF ROWS WITH CONSTANTS
Dim i As Integer
For i = 2 To "" & n1 & ""
'SETUP STARTING PLACE FOR i TO BEGIN CHECKING THE DATA TO MATCH ESTABLISHMENT
Let Dis = Sheets("Data File").Range("A" & i & "")
'CHECK EACH i FOR MATCHING if matching THEN follow instructions
If Dis = Sheets("OSHA Form 300").Range("K11").Value Then
'===============================================================================================
'BEGIN COPING DATA FROM ONE SHEET TO THE OTHER
'===============================================================================================
'THIS MOST LIKELY COULD BE SHORTENED BY SOMEONE WITH MORE EXPERIENCE. I WOULD LIKE TO SEE HOW IF
'SOMEONE HAS THE TIME PLEASE
'EMPLOYEE NAME
Sheets("Data File").Range("C" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'B
'JOB TITLE
Sheets("Data File").Range("D" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'C
'DATE OF INJURY
Sheets("Data File").Range("E" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'D
'INJURY LOCATION
Sheets("Data File").Range("F" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'E
'DESCRIPTION
Sheets("Data File").Range("G" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'F
'DEATH
Sheets("Data File").Range("N" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'G
'AWAY FROM WORK
Sheets("Data File").Range("O" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'H
'TRANSFER OR RESTRICTION
Sheets("Data File").Range("P" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'I
'OTHER RECORD ABLE CASES
Sheets("Data File").Range("Q" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'J
'AWAY FROM WORK
'Sheets("Data File").Range("D" & i & "").Copy
'Sheets("OSHA Form 300").Activate
'Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'K
'TRANSFER / RESTRICTION DAYS
'Sheets("Data File").Range("D" & i & "").Copy
'Sheets("OSHA Form 300").Activate
'Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'L
'INJURY
Sheets("Data File").Range("H" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'M
'SKIN DISORDER
Sheets("Data File").Range("I" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'N
'RESPIRATORY CONDITION
Sheets("Data File").Range("J" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'O
'POISONING
Sheets("Data File").Range("K" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'P
'HEARING LOSS
Sheets("Data File").Range("L" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(0, 1).Select 'Q
'ALL OTHER ILLNESSSES
Sheets("Data File").Range("M" & i & "").Copy
Sheets("OSHA Form 300").Activate
Selection.PasteSpecial Paste:=xlPasteValues
'NEXT EMPLOYEE
ActiveCell.Offset(1, -16).Select 'R
End If
Next i
Sheets("OSHA Form 300").Range("D25:D128").NumberFormat = "m/d/yyyy"
Sheets("OSHA Form 300").Select
Range("B25").Select
ENDMACRO:
End Sub
Bookmarks