Hello,

I am looking for a way to mirror a value from one sheet to another, in the
same workbook. I currently have a table with a named range of values
"vUtilityUsage_Basic". I want those values to be updated from a certain table
depending on the value of a drop down list called "vUtility_Company". Here is
what I got, can someone help me correct this?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrH:
Application.EnableEvents = False
Select Case LCase(Me.Range("vUtility_Company").Value)
Case LCase("PGE Residential")
Call PGE_Res_WriteUsage
Case LCase("PGE Business")
Call PGE_Bus_WriteUsage
Case LCase("SMUD Residential")
Call SMUD_Res_WriteUsage
Case Else
'do nothing, just continue to the end sub
End Select
ErrH:
Application.EnableEvents = True
End Sub
Sub PGE_Res_WriteUsage()
Me.Range("vPGE_Res_E1Usage") = Target.Value
End Sub
Sub PGE_Bus_WriteUsage()
Me.Range("vPGE_Bus_A1Usage") = Target.Value
End Sub
Sub SMUD_Res_WriteUsage()
Me.Range("vSMUD_Res_Usage") = Target.Value
End Sub

You need to pass the target through to the subs...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrH:
Application.EnableEvents = False
Select Case LCase(Me.Range("vUtility_Company").Value)
Case LCase("PGE Residential")
Call PGE_Res_WriteUsage(Target)
Case LCase("PGE Business")
Call PGE_Bus_WriteUsage(Target)
Case LCase("SMUD Residential")
Call SMUD_Res_WriteUsage(Target)
Case Else
'do nothing, just continue to the end sub
End Select
ErrH:
Application.EnableEvents = True
End Sub
Sub PGE_Res_WriteUsage(ByVal Target As Range)
Me.Range("vPGE_Res_E1Usage") = Target.Value
End Sub
Sub PGE_Bus_WriteUsage(ByVal Target As Range)
Me.Range("vPGE_Bus_A1Usage") = Target.Value
End Sub
Sub SMUD_Res_WriteUsage(ByVal Target As Range)
Me.Range("vSMUD_Res_Usage") = Target.Value
End Sub

--
HTH...

Jim Thomlinson


"Eric" wrote:

Hello,

I am looking for a way to mirror a value from one sheet to another, in the
same workbook. I currently have a table with a named range of values
"vUtilityUsage_Basic". I want those values to be updated from a certain table
depending on the value of a drop down list called "vUtility_Company". Here is
what I got, can someone help me correct this?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrH:
Application.EnableEvents = False
Select Case LCase(Me.Range("vUtility_Company").Value)
Case LCase("PGE Residential")
Call PGE_Res_WriteUsage
Case LCase("PGE Business")
Call PGE_Bus_WriteUsage
Case LCase("SMUD Residential")
Call SMUD_Res_WriteUsage
Case Else
'do nothing, just continue to the end sub
End Select
ErrH:
Application.EnableEvents = True
End Sub
Sub PGE_Res_WriteUsage()
Me.Range("vPGE_Res_E1Usage") = Target.Value
End Sub
Sub PGE_Bus_WriteUsage()
Me.Range("vPGE_Bus_A1Usage") = Target.Value
End Sub
Sub SMUD_Res_WriteUsage()
Me.Range("vSMUD_Res_Usage") = Target.Value
End Sub

Hello Jim,

A couple of questions:

1. Would I put the code on the sheet with the "vUtility_Company" or the
sheet where the data is being written? If it goes needs to go where the
"vUtility_Company" data validation list is, how would I add it to an existing
Private Sub Worksheet_Change(ByVal Target As Range) ? Currently it has 2
select cases using an If Not code.

2. After looking through the code I realized, I failed to say where to write
the data. I want the data written to a table with the name
"vUtilityUsage_Basic". I should also mention the data being written is from a
table the same size (3 Columns 12 Rows). How would I correct the code to make
this happen?

Thanks again for all the great help,

"Jim Thomlinson" wrote:

You need to pass the target through to the subs...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrH:
Application.EnableEvents = False
Select Case LCase(Me.Range("vUtility_Company").Value)
Case LCase("PGE Residential")
Call PGE_Res_WriteUsage(Target)
Case LCase("PGE Business")
Call PGE_Bus_WriteUsage(Target)
Case LCase("SMUD Residential")
Call SMUD_Res_WriteUsage(Target)
Case Else
'do nothing, just continue to the end sub
End Select
ErrH:
Application.EnableEvents = True
End Sub
Sub PGE_Res_WriteUsage(ByVal Target As Range)
Me.Range("vPGE_Res_E1Usage") = Target.Value
End Sub
Sub PGE_Bus_WriteUsage(ByVal Target As Range)
Me.Range("vPGE_Bus_A1Usage") = Target.Value
End Sub
Sub SMUD_Res_WriteUsage(ByVal Target As Range)
Me.Range("vSMUD_Res_Usage") = Target.Value
End Sub

--
HTH...

Jim Thomlinson


"Eric" wrote:

Hello,

I am looking for a way to mirror a value from one sheet to another, in the
same workbook. I currently have a table with a named range of values
"vUtilityUsage_Basic". I want those values to be updated from a certain table
depending on the value of a drop down list called "vUtility_Company". Here is
what I got, can someone help me correct this?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrH:
Application.EnableEvents = False
Select Case LCase(Me.Range("vUtility_Company").Value)
Case LCase("PGE Residential")
Call PGE_Res_WriteUsage
Case LCase("PGE Business")
Call PGE_Bus_WriteUsage
Case LCase("SMUD Residential")
Call SMUD_Res_WriteUsage
Case Else
'do nothing, just continue to the end sub
End Select
ErrH:
Application.EnableEvents = True
End Sub
Sub PGE_Res_WriteUsage()
Me.Range("vPGE_Res_E1Usage") = Target.Value
End Sub
Sub PGE_Bus_WriteUsage()
Me.Range("vPGE_Bus_A1Usage") = Target.Value
End Sub
Sub SMUD_Res_WriteUsage()
Me.Range("vSMUD_Res_Usage") = Target.Value
End Sub