I have 2 Sets of Code...
Inserts information but i am having to do a call for each routine as you can see...
Can this be cleaned up into One VBA instead of calling Multiple routines and unprotecting and reprotectiing for each Sub to speed this up a bit...I know its a mess...but im not a VBA guru..I kinda pieced this together...but it does work..
This Loads the information...
This code is the same but clears the Load..
Thank you in advance!!!
Inserts information but i am having to do a call for each routine as you can see...
Can this be cleaned up into One VBA instead of calling Multiple routines and unprotecting and reprotectiing for each Sub to speed this up a bit...I know its a mess...but im not a VBA guru..I kinda pieced this together...but it does work..
This Loads the information...
VBA Code:
Sub InsertFormula()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("F3").Formula2 = "=IF(FILTER(Database!$C$2:$H$2000,Database!$B$2:$B$2000=$D$3,""Not In Database Yet"")=0,"""",FILTER(Database!$C$2:$H$2000,Database!$B$2:$B$2000=$D$3,""Not In Database Yet""))"
Sheets(i).Protect
Next
Call InsertFormulaReaders
End Sub
Sub InsertFormulaReaders()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("L3:L2000").Formula2 = "=UPPER(IF(G3<>"""",""("" & $D$3 & "") "" & G3 & """" & H3 &"""",""""))"
Sheets(i).Protect
Next
Call InsertFormulaRDR
End Sub
Sub InsertFormulaRDR()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("R3:R2000").Formula2 = "=IF(G3<>"""",""("" & $D$3 & "") "" & G3 & "" - RDR"","""")"
Sheets(i).Protect
Next
Call InsertFormulaDC
End Sub
Sub InsertFormulaDC()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("S3:S2000").Formula2 = "=IF(G3<>"""",""("" & $D$3 & "") "" & G3 & "" - DC"","""")"
Sheets(i).Protect
Next
Call InsertFormulaREX
End Sub
Sub InsertFormulaREX()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("T3:T2000").Formula2 = "=IF(G3<>"""",""("" & $D$3 & "") "" & G3 & "" - REX"","""")"
Sheets(i).Protect
Next
Call InsertFormulaLK
End Sub
Sub InsertFormulaLK()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("U3:U2000").Formula2 = "=IF(G3<>"""",""("" & $D$3 & "") "" & G3 & "" - LK"","""")"
Sheets(i).Protect
Next
Sheets("Site TOC").Select
Sheets("Site TOC").Unprotect
ActiveSheet.Tab.ColorIndex = 2
Sheets("Site TOC").Protect
End Sub
This code is the same but clears the Load..
VBA Code:
Sub ClearFormula()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("F3").ClearContents
Sheets(i).Protect
Next
Call ClearFormulaReaders
End Sub
Sub ClearFormulaReaders()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("L3:L2000").ClearContents
Sheets(i).Protect
Next
Call ClearFormulaRDR
End Sub
Sub ClearFormulaRDR()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("R3:R2000").ClearContents
Sheets(i).Protect
Next
Call ClearFormulaDC
End Sub
Sub ClearFormulaDC()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("S3:S2000").ClearContents
Sheets(i).Protect
Next
Call ClearFormulaREX
End Sub
Sub ClearFormulaREX()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("T3:T2000").ClearContents
Sheets(i).Protect
Next
Call ClearFormulaLK
End Sub
Sub ClearFormulaLK()
Dim i As Long
For i = Sheets("Ignore1").Index + 1 To Sheets("Ignore2").Index - 1
Sheets(i).Unprotect
Sheets(i).Range("U3:U2000").ClearContents
Sheets(i).Protect
Next
Sheets("Site TOC").Select
Sheets("Site TOC").Unprotect
ActiveSheet.Tab.ColorIndex = 2
Sheets("Site TOC").Protect
End Sub
Thank you in advance!!!
Last edited: