I am trying to copy data in Sheet1 to varying cell locations in other sheets within my workbook. Everything works as desired; however, it appears that every bit of code is running every time I enter data. This creates a significant lag in data entry.
How can I speed up the process?
Below is the code I have in Sheet1......
Private Sub Worksheet_Change(ByVal target As Range)
worksheet_change_A target
worksheet_change_B target
worksheet_change_C target
worksheet_change_D target
worksheet_change_E target
worksheet_change_F target
worksheet_change_G target
End Sub
Private Sub worksheet_change_A(ByVal target As Range)
'Tab names
Set target = Range("b12:b19")
If Sheet1.Range("b12").Value = "" Then
Sheet2.Name = "Quote1"
Else
Sheet2.Name = Sheet1.Range("b12").Value
End If
If Sheet1.Range("b13").Value = "" Then
Sheet3.Name = "Quote2"
Else
Sheet3.Name = Sheet1.Range("b13").Value
End If
If Sheet1.Range("b14").Value = "" Then
Sheet4.Name = "Quote3"
Else
Sheet4.Name = Sheet1.Range("b14").Value
End If
If Sheet1.Range("b15").Value = "" Then
Sheet5.Name = "Quote4"
Else
Sheet5.Name = Sheet1.Range("b15").Value
End If
If Sheet1.Range("b16").Value = "" Then
Sheet6.Name = "Quote5"
Else
Sheet6.Name = Sheet1.Range("b16").Value
End If
If Sheet1.Range("b17").Value = "" Then
Sheet7.Name = "Quote6"
Else
Sheet7.Name = Sheet1.Range("b17").Value
End If
If Sheet1.Range("b18").Value = "" Then
Sheet8.Name = "Quote7"
Else
Sheet8.Name = Sheet1.Range("b18").Value
End If
If Sheet1.Range("b19").Value = "" Then
Sheet9.Name = "Quote8"
Else
Sheet9.Name = Sheet1.Range("b19").Value
End If
End Sub
Private Sub worksheet_change_B(ByVal target As Range)
'Schedule number
Set target = Range("d1")
If Sheet1.Range("d1").Value <> "" Then
Sheet2.Range("b1") = Sheet1.Range("d1").Value
Sheet3.Range("b1") = Sheet1.Range("d1").Value
Sheet4.Range("b1") = Sheet1.Range("d1").Value
Sheet5.Range("b1") = Sheet1.Range("d1").Value
Sheet6.Range("b1") = Sheet1.Range("d1").Value
Sheet7.Range("b1") = Sheet1.Range("d1").Value
Sheet8.Range("b1") = Sheet1.Range("d1").Value
Sheet9.Range("b1") = Sheet1.Range("d1").Value
Else
Sheet2.Range("b1") = ""
Sheet3.Range("b1") = ""
Sheet4.Range("b1") = ""
Sheet5.Range("b1") = ""
Sheet6.Range("b1") = ""
Sheet7.Range("b1") = ""
Sheet8.Range("b1") = ""
Sheet9.Range("b1") = ""
End If
End Sub
Private Sub worksheet_change_C(ByVal target As Range)
'Company name
Set target = Range("d2")
If Sheet1.Range("d2").Value <> "" Then
Sheet2.Range("e1") = Sheet1.Range("d2").Value
Sheet3.Range("e1") = Sheet1.Range("d2").Value
Sheet4.Range("e1") = Sheet1.Range("d2").Value
Sheet5.Range("e1") = Sheet1.Range("d2").Value
Sheet6.Range("e1") = Sheet1.Range("d2").Value
Sheet7.Range("e1") = Sheet1.Range("d2").Value
Sheet8.Range("e1") = Sheet1.Range("d2").Value
Sheet9.Range("e1") = Sheet1.Range("d2").Value
Else
Sheet2.Range("e1") = ""
Sheet3.Range("e1") = ""
Sheet4.Range("e1") = ""
Sheet5.Range("e1") = ""
Sheet6.Range("e1") = ""
Sheet7.Range("e1") = ""
Sheet8.Range("e1") = ""
Sheet9.Range("e1") = ""
End If
End Sub
Private Sub worksheet_change_D(ByVal target As Range)
'Job titles
Set target = Range("b12:b19")
If Sheet1.Range("b12").Value <> "" Then
Sheet2.Range("d2") = Sheet1.Range("b12").Value
Sheet3.Range("d2") = Sheet1.Range("b13").Value
Sheet4.Range("d2") = Sheet1.Range("b14").Value
Sheet5.Range("d2") = Sheet1.Range("b15").Value
Sheet6.Range("d2") = Sheet1.Range("b16").Value
Sheet7.Range("d2") = Sheet1.Range("b17").Value
Sheet8.Range("d2") = Sheet1.Range("b18").Value
Sheet9.Range("d2") = Sheet1.Range("b19").Value
Else
Sheet2.Range("d2") = ""
Sheet3.Range("d2") = ""
Sheet4.Range("d2") = ""
Sheet5.Range("d2") = ""
Sheet6.Range("d2") = ""
Sheet7.Range("d2") = ""
Sheet8.Range("d2") = ""
Sheet9.Range("d2") = ""
End If
End Sub
Private Sub worksheet_change_E(ByVal target As Range)
'FT employment count
Set target = Range("f12:f19")
If Sheet1.Range("f12").Value <> "" Then
Sheet2.Range("c3") = Sheet1.Range("f12").Value
Sheet3.Range("c3") = Sheet1.Range("f13").Value
Sheet4.Range("c3") = Sheet1.Range("f14").Value
Sheet5.Range("c3") = Sheet1.Range("f15").Value
Sheet6.Range("c3") = Sheet1.Range("f16").Value
Sheet7.Range("c3") = Sheet1.Range("f17").Value
Sheet8.Range("c3") = Sheet1.Range("f18").Value
Sheet9.Range("c3") = Sheet1.Range("f19").Value
Else
Sheet2.Range("c3") = ""
Sheet3.Range("c3") = ""
Sheet4.Range("c3") = ""
Sheet5.Range("c3") = ""
Sheet6.Range("c3") = ""
Sheet7.Range("c3") = ""
Sheet8.Range("c3") = ""
Sheet9.Range("c3") = ""
End If
End Sub
Private Sub worksheet_change_F(ByVal target As Range)
'PT employment count
Set target = Range("g12:g19")
If Sheet1.Range("g12").Value <> "" Then
Sheet2.Range("e3") = Sheet1.Range("g12").Value
Sheet3.Range("e3") = Sheet1.Range("g13").Value
Sheet4.Range("e3") = Sheet1.Range("g14").Value
Sheet5.Range("e3") = Sheet1.Range("g15").Value
Sheet6.Range("e3") = Sheet1.Range("g16").Value
Sheet7.Range("e3") = Sheet1.Range("g17").Value
Sheet8.Range("e3") = Sheet1.Range("g18").Value
Sheet9.Range("e3") = Sheet1.Range("g19").Value
Else
Sheet2.Range("e3") = ""
Sheet3.Range("e3") = ""
Sheet4.Range("e3") = ""
Sheet5.Range("e3") = ""
Sheet6.Range("e3") = ""
Sheet7.Range("e3") = ""
Sheet8.Range("e3") = ""
Sheet9.Range("e3") = ""
End If
End Sub
Private Sub worksheet_change_G(ByVal target As Range)
'Quote respondent name
Set target = Range("h12:h19")
If Sheet1.Range("h12").Value <> "" Then
Sheet2.Range("n3") = Sheet1.Range("h12").Value
Sheet3.Range("n3") = Sheet1.Range("h13").Value
Sheet4.Range("n3") = Sheet1.Range("h14").Value
Sheet5.Range("n3") = Sheet1.Range("h15").Value
Sheet6.Range("n3") = Sheet1.Range("h16").Value
Sheet7.Range("n3") = Sheet1.Range("h17").Value
Sheet8.Range("n3") = Sheet1.Range("h18").Value
Sheet9.Range("n3") = Sheet1.Range("h19").Value
Else
Sheet2.Range("n3") = ""
Sheet3.Range("n3") = ""
Sheet4.Range("n3") = ""
Sheet5.Range("n3") = ""
Sheet6.Range("n3") = ""
Sheet7.Range("n3") = ""
Sheet8.Range("n3") = ""
Sheet9.Range("n3") = ""
End If
End Sub
How can I speed up the process?
Below is the code I have in Sheet1......
Private Sub Worksheet_Change(ByVal target As Range)
worksheet_change_A target
worksheet_change_B target
worksheet_change_C target
worksheet_change_D target
worksheet_change_E target
worksheet_change_F target
worksheet_change_G target
End Sub
Private Sub worksheet_change_A(ByVal target As Range)
'Tab names
Set target = Range("b12:b19")
If Sheet1.Range("b12").Value = "" Then
Sheet2.Name = "Quote1"
Else
Sheet2.Name = Sheet1.Range("b12").Value
End If
If Sheet1.Range("b13").Value = "" Then
Sheet3.Name = "Quote2"
Else
Sheet3.Name = Sheet1.Range("b13").Value
End If
If Sheet1.Range("b14").Value = "" Then
Sheet4.Name = "Quote3"
Else
Sheet4.Name = Sheet1.Range("b14").Value
End If
If Sheet1.Range("b15").Value = "" Then
Sheet5.Name = "Quote4"
Else
Sheet5.Name = Sheet1.Range("b15").Value
End If
If Sheet1.Range("b16").Value = "" Then
Sheet6.Name = "Quote5"
Else
Sheet6.Name = Sheet1.Range("b16").Value
End If
If Sheet1.Range("b17").Value = "" Then
Sheet7.Name = "Quote6"
Else
Sheet7.Name = Sheet1.Range("b17").Value
End If
If Sheet1.Range("b18").Value = "" Then
Sheet8.Name = "Quote7"
Else
Sheet8.Name = Sheet1.Range("b18").Value
End If
If Sheet1.Range("b19").Value = "" Then
Sheet9.Name = "Quote8"
Else
Sheet9.Name = Sheet1.Range("b19").Value
End If
End Sub
Private Sub worksheet_change_B(ByVal target As Range)
'Schedule number
Set target = Range("d1")
If Sheet1.Range("d1").Value <> "" Then
Sheet2.Range("b1") = Sheet1.Range("d1").Value
Sheet3.Range("b1") = Sheet1.Range("d1").Value
Sheet4.Range("b1") = Sheet1.Range("d1").Value
Sheet5.Range("b1") = Sheet1.Range("d1").Value
Sheet6.Range("b1") = Sheet1.Range("d1").Value
Sheet7.Range("b1") = Sheet1.Range("d1").Value
Sheet8.Range("b1") = Sheet1.Range("d1").Value
Sheet9.Range("b1") = Sheet1.Range("d1").Value
Else
Sheet2.Range("b1") = ""
Sheet3.Range("b1") = ""
Sheet4.Range("b1") = ""
Sheet5.Range("b1") = ""
Sheet6.Range("b1") = ""
Sheet7.Range("b1") = ""
Sheet8.Range("b1") = ""
Sheet9.Range("b1") = ""
End If
End Sub
Private Sub worksheet_change_C(ByVal target As Range)
'Company name
Set target = Range("d2")
If Sheet1.Range("d2").Value <> "" Then
Sheet2.Range("e1") = Sheet1.Range("d2").Value
Sheet3.Range("e1") = Sheet1.Range("d2").Value
Sheet4.Range("e1") = Sheet1.Range("d2").Value
Sheet5.Range("e1") = Sheet1.Range("d2").Value
Sheet6.Range("e1") = Sheet1.Range("d2").Value
Sheet7.Range("e1") = Sheet1.Range("d2").Value
Sheet8.Range("e1") = Sheet1.Range("d2").Value
Sheet9.Range("e1") = Sheet1.Range("d2").Value
Else
Sheet2.Range("e1") = ""
Sheet3.Range("e1") = ""
Sheet4.Range("e1") = ""
Sheet5.Range("e1") = ""
Sheet6.Range("e1") = ""
Sheet7.Range("e1") = ""
Sheet8.Range("e1") = ""
Sheet9.Range("e1") = ""
End If
End Sub
Private Sub worksheet_change_D(ByVal target As Range)
'Job titles
Set target = Range("b12:b19")
If Sheet1.Range("b12").Value <> "" Then
Sheet2.Range("d2") = Sheet1.Range("b12").Value
Sheet3.Range("d2") = Sheet1.Range("b13").Value
Sheet4.Range("d2") = Sheet1.Range("b14").Value
Sheet5.Range("d2") = Sheet1.Range("b15").Value
Sheet6.Range("d2") = Sheet1.Range("b16").Value
Sheet7.Range("d2") = Sheet1.Range("b17").Value
Sheet8.Range("d2") = Sheet1.Range("b18").Value
Sheet9.Range("d2") = Sheet1.Range("b19").Value
Else
Sheet2.Range("d2") = ""
Sheet3.Range("d2") = ""
Sheet4.Range("d2") = ""
Sheet5.Range("d2") = ""
Sheet6.Range("d2") = ""
Sheet7.Range("d2") = ""
Sheet8.Range("d2") = ""
Sheet9.Range("d2") = ""
End If
End Sub
Private Sub worksheet_change_E(ByVal target As Range)
'FT employment count
Set target = Range("f12:f19")
If Sheet1.Range("f12").Value <> "" Then
Sheet2.Range("c3") = Sheet1.Range("f12").Value
Sheet3.Range("c3") = Sheet1.Range("f13").Value
Sheet4.Range("c3") = Sheet1.Range("f14").Value
Sheet5.Range("c3") = Sheet1.Range("f15").Value
Sheet6.Range("c3") = Sheet1.Range("f16").Value
Sheet7.Range("c3") = Sheet1.Range("f17").Value
Sheet8.Range("c3") = Sheet1.Range("f18").Value
Sheet9.Range("c3") = Sheet1.Range("f19").Value
Else
Sheet2.Range("c3") = ""
Sheet3.Range("c3") = ""
Sheet4.Range("c3") = ""
Sheet5.Range("c3") = ""
Sheet6.Range("c3") = ""
Sheet7.Range("c3") = ""
Sheet8.Range("c3") = ""
Sheet9.Range("c3") = ""
End If
End Sub
Private Sub worksheet_change_F(ByVal target As Range)
'PT employment count
Set target = Range("g12:g19")
If Sheet1.Range("g12").Value <> "" Then
Sheet2.Range("e3") = Sheet1.Range("g12").Value
Sheet3.Range("e3") = Sheet1.Range("g13").Value
Sheet4.Range("e3") = Sheet1.Range("g14").Value
Sheet5.Range("e3") = Sheet1.Range("g15").Value
Sheet6.Range("e3") = Sheet1.Range("g16").Value
Sheet7.Range("e3") = Sheet1.Range("g17").Value
Sheet8.Range("e3") = Sheet1.Range("g18").Value
Sheet9.Range("e3") = Sheet1.Range("g19").Value
Else
Sheet2.Range("e3") = ""
Sheet3.Range("e3") = ""
Sheet4.Range("e3") = ""
Sheet5.Range("e3") = ""
Sheet6.Range("e3") = ""
Sheet7.Range("e3") = ""
Sheet8.Range("e3") = ""
Sheet9.Range("e3") = ""
End If
End Sub
Private Sub worksheet_change_G(ByVal target As Range)
'Quote respondent name
Set target = Range("h12:h19")
If Sheet1.Range("h12").Value <> "" Then
Sheet2.Range("n3") = Sheet1.Range("h12").Value
Sheet3.Range("n3") = Sheet1.Range("h13").Value
Sheet4.Range("n3") = Sheet1.Range("h14").Value
Sheet5.Range("n3") = Sheet1.Range("h15").Value
Sheet6.Range("n3") = Sheet1.Range("h16").Value
Sheet7.Range("n3") = Sheet1.Range("h17").Value
Sheet8.Range("n3") = Sheet1.Range("h18").Value
Sheet9.Range("n3") = Sheet1.Range("h19").Value
Else
Sheet2.Range("n3") = ""
Sheet3.Range("n3") = ""
Sheet4.Range("n3") = ""
Sheet5.Range("n3") = ""
Sheet6.Range("n3") = ""
Sheet7.Range("n3") = ""
Sheet8.Range("n3") = ""
Sheet9.Range("n3") = ""
End If
End Sub