Hello,

I can't figure out what is wrong with the following code. Everything works,
except for the "'subs to copy utility usage data" My goal with these subs is
to copy data from one table to another. I want all changes to the data to be
immediately updated. Also the data is always entered into the table (which is
named and has 3 columns and 13 rows) on the left side of the equation to a
table on the right side of the code . I should mention that the named table
data I am copying doesn't include the table headers. I am fairly new to VBA
code, can somebody tell me where I went wrong? Here is my code:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Hide/Unhide Rows when you change Utility Company on the phone script
If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
Select Case LCase(Me.Range("vUtility_Company").Value)
Case LCase("")
Call PS_MinimizeALL
Case LCase("PGE Residential")
Call PS_MinimizeALL
Call PGE_Res
Call PGE_Res_WriteUsage
Case LCase("PGE Business")
Call PS_MinimizeALL
Call PGE_Bus
Call PGE_Bus_WriteUsage
Case LCase("SMUD Residential")
Call PS_MinimizeALL
Call SMUD_Res
Call SMUD_Res_WriteUsage
Case LCase("Debug")
Call Show_All
Case Else
'do nothing, just continue to the end sub
End Select
Else
'Hide/Unhide Rows when you change Rent/Own Value
If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
Select Case LCase(Me.Range("vRentOwn").Value)
Case LCase("")
Call PS_MinimizeRentOwn
Case LCase("Rent")
Call PS_MaximizeRentOwn
Case LCase("Own")
Call PS_MinimizeRentOwn
Case Else
End Select
End If
End If

End Sub
'Subs for Utility Company
Sub Show_All()
Me.Rows("30:1000").EntireRow.Hidden = False
End Sub
Sub PS_MinimizeALL()
Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
End Sub
Sub PGE_Res()
Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
End Sub
Sub PGE_Bus()
Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
End Sub
Sub SMUD_Res()
Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
End Sub
'Subs for Rent/Own
Sub PS_MinimizeRentOwn()
Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
End Sub
Sub PS_MaximizeRentOwn()
Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
End Sub
'Subs for copying utility usage data
Sub PGE_Res_WriteUsage()
Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
End Sub
Sub PGE_Bus_WriteUsage()
Me.Range("vPGE_Bus_A1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
End Sub
Sub SMUD_Res_WriteUsage()
Me.Range("vSMUD_Res_Usage").Value = Me.Range("vUtilityUsage_Basic").Value
End Sub

Hi Eric,

I can't actually see anything wrong in the code. Try the following and
confirm that you have the correct ranges in the named ranges. Each time the
code stops, change windows to the worksheet and check that the correct range
is selected for the particular named range.

The msgboxs will also confirm that the sub is being called.

'Subs for copying utility usage data
Sub PGE_Res_WriteUsage()

'Confirm range of named range
Me.Range("vPGE_Res_E1Usage").Select
Stop 'change window to worksheet and check selected range

'Confirm range of named range
Me.Range("vUtilityUsage_Basic").Select
Stop 'change window to worksheet and check selected range

Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
End Sub

--
Regards,

OssieMac


"Eric" wrote:

> Hello,
>
> I can't figure out what is wrong with the following code. Everything works,
> except for the "'subs to copy utility usage data" My goal with these subs is
> to copy data from one table to another. I want all changes to the data to be
> immediately updated. Also the data is always entered into the table (which is
> named and has 3 columns and 13 rows) on the left side of the equation to a
> table on the right side of the code . I should mention that the named table
> data I am copying doesn't include the table headers. I am fairly new to VBA
> code, can somebody tell me where I went wrong? Here is my code:
>
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> 'Hide/Unhide Rows when you change Utility Company on the phone script
> If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
> Select Case LCase(Me.Range("vUtility_Company").Value)
> Case LCase("")
> Call PS_MinimizeALL
> Case LCase("PGE Residential")
> Call PS_MinimizeALL
> Call PGE_Res
> Call PGE_Res_WriteUsage
> Case LCase("PGE Business")
> Call PS_MinimizeALL
> Call PGE_Bus
> Call PGE_Bus_WriteUsage
> Case LCase("SMUD Residential")
> Call PS_MinimizeALL
> Call SMUD_Res
> Call SMUD_Res_WriteUsage
> Case LCase("Debug")
> Call Show_All
> Case Else
> 'do nothing, just continue to the end sub
> End Select
> Else
> 'Hide/Unhide Rows when you change Rent/Own Value
> If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
> Select Case LCase(Me.Range("vRentOwn").Value)
> Case LCase("")
> Call PS_MinimizeRentOwn
> Case LCase("Rent")
> Call PS_MaximizeRentOwn
> Case LCase("Own")
> Call PS_MinimizeRentOwn
> Case Else
> End Select
> End If
> End If
>
> End Sub
> 'Subs for Utility Company
> Sub Show_All()
> Me.Rows("30:1000").EntireRow.Hidden = False
> End Sub
> Sub PS_MinimizeALL()
> Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
> End Sub
> Sub PGE_Res()
> Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
> End Sub
> Sub PGE_Bus()
> Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
> End Sub
> Sub SMUD_Res()
> Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
> End Sub
> 'Subs for Rent/Own
> Sub PS_MinimizeRentOwn()
> Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
> End Sub
> Sub PS_MaximizeRentOwn()
> Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
> End Sub
> 'Subs for copying utility usage data
> Sub PGE_Res_WriteUsage()
> Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> End Sub
> Sub PGE_Bus_WriteUsage()
> Me.Range("vPGE_Bus_A1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> End Sub
> Sub SMUD_Res_WriteUsage()
> Me.Range("vSMUD_Res_Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> End Sub

Hello,

It works fine for the following code:

'Confirm range of named range
Me.Range("vPGE_Res_E1Usage").Select
Stop 'change window to worksheet and check selected range

However I get an error when I run the following code:

'Confirm range of named range
Me.Range("vUtilityUsage_Basic").Select
Stop 'change window to worksheet and check selected range

Which says:
Run-time error '1004':
Method 'Range' of object'_Worksheet' failed

I have made sure that the name is accurate, any ideas why this would happen?
I should probably also add that the second Range vUtilityUsage_Basic refers
only to the first three colums of a table (it has 5 total) while the first
range vPGE_Res_E1Usage is the whole table (it only has 3 columns total). Both
have the same number of rows, except the second table has a subtotal bar. I
would appreciate any help.


"OssieMac" wrote:

> Hi Eric,
>
> I can't actually see anything wrong in the code. Try the following and
> confirm that you have the correct ranges in the named ranges. Each time the
> code stops, change windows to the worksheet and check that the correct range
> is selected for the particular named range.
>
> The msgboxs will also confirm that the sub is being called.
>
> 'Subs for copying utility usage data
> Sub PGE_Res_WriteUsage()
>
> 'Confirm range of named range
> Me.Range("vPGE_Res_E1Usage").Select
> Stop 'change window to worksheet and check selected range
>
> 'Confirm range of named range
> Me.Range("vUtilityUsage_Basic").Select
> Stop 'change window to worksheet and check selected range
>
> Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> End Sub
>
> --
> Regards,
>
> OssieMac
>
>
> "Eric" wrote:
>
> > Hello,
> >
> > I can't figure out what is wrong with the following code. Everything works,
> > except for the "'subs to copy utility usage data" My goal with these subs is
> > to copy data from one table to another. I want all changes to the data to be
> > immediately updated. Also the data is always entered into the table (which is
> > named and has 3 columns and 13 rows) on the left side of the equation to a
> > table on the right side of the code . I should mention that the named table
> > data I am copying doesn't include the table headers. I am fairly new to VBA
> > code, can somebody tell me where I went wrong? Here is my code:
> >
> >
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > 'Hide/Unhide Rows when you change Utility Company on the phone script
> > If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
> > Select Case LCase(Me.Range("vUtility_Company").Value)
> > Case LCase("")
> > Call PS_MinimizeALL
> > Case LCase("PGE Residential")
> > Call PS_MinimizeALL
> > Call PGE_Res
> > Call PGE_Res_WriteUsage
> > Case LCase("PGE Business")
> > Call PS_MinimizeALL
> > Call PGE_Bus
> > Call PGE_Bus_WriteUsage
> > Case LCase("SMUD Residential")
> > Call PS_MinimizeALL
> > Call SMUD_Res
> > Call SMUD_Res_WriteUsage
> > Case LCase("Debug")
> > Call Show_All
> > Case Else
> > 'do nothing, just continue to the end sub
> > End Select
> > Else
> > 'Hide/Unhide Rows when you change Rent/Own Value
> > If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
> > Select Case LCase(Me.Range("vRentOwn").Value)
> > Case LCase("")
> > Call PS_MinimizeRentOwn
> > Case LCase("Rent")
> > Call PS_MaximizeRentOwn
> > Case LCase("Own")
> > Call PS_MinimizeRentOwn
> > Case Else
> > End Select
> > End If
> > End If
> >
> > End Sub
> > 'Subs for Utility Company
> > Sub Show_All()
> > Me.Rows("30:1000").EntireRow.Hidden = False
> > End Sub
> > Sub PS_MinimizeALL()
> > Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
> > End Sub
> > Sub PGE_Res()
> > Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
> > End Sub
> > Sub PGE_Bus()
> > Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
> > End Sub
> > Sub SMUD_Res()
> > Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
> > End Sub
> > 'Subs for Rent/Own
> > Sub PS_MinimizeRentOwn()
> > Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
> > End Sub
> > Sub PS_MaximizeRentOwn()
> > Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
> > End Sub
> > 'Subs for copying utility usage data
> > Sub PGE_Res_WriteUsage()
> > Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > End Sub
> > Sub PGE_Bus_WriteUsage()
> > Me.Range("vPGE_Bus_A1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > End Sub
> > Sub SMUD_Res_WriteUsage()
> > Me.Range("vSMUD_Res_Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > End Sub

Hi again Eric,

The error suggests one of the following
The range is not named
Your spelling of range name is incorrect
The named range is on another worksheet. You do realize that Me. refers to
the worksheet on which the Change has occurred. That is the worksheet
belonging to the sub
Private Sub Worksheet_Change(ByVal Target As Range)
If the range name belongs to another worksheet then reference it with the
appropriate worksheet name as follows
Sheets("Sheet2").Range("vUtilityUsage_Basic") or
Sheet2.Range("vUtilityUsage_Basic")
The latter being the CodeName which is my preference because if the user
changes the worksheet name, the CodeName does not change. The CodeName can be
obtained from the VBA Editor window in the Project Explorer section at the
left. The CodeName is the one that is NOT in parenthesis. The name in
parenthesis is the name that can be changed by the user.

Of course you cannot actually select any range unless the worksheet with the
range is the ActiveSheet so my test would require activating that worksheet
first.

However, you can use code like this
Me.Range("vPGE_Res_E1Usage").Value = _
Sheet2.Range("vUtilityUsage_Basic").Value


--
Regards,

OssieMac


"Eric" wrote:

> Hello,
>
> It works fine for the following code:
>
> 'Confirm range of named range
> Me.Range("vPGE_Res_E1Usage").Select
> Stop 'change window to worksheet and check selected range
>
> However I get an error when I run the following code:
>
> 'Confirm range of named range
> Me.Range("vUtilityUsage_Basic").Select
> Stop 'change window to worksheet and check selected range
>
> Which says:
> Run-time error '1004':
> Method 'Range' of object'_Worksheet' failed
>
> I have made sure that the name is accurate, any ideas why this would happen?
> I should probably also add that the second Range vUtilityUsage_Basic refers
> only to the first three colums of a table (it has 5 total) while the first
> range vPGE_Res_E1Usage is the whole table (it only has 3 columns total). Both
> have the same number of rows, except the second table has a subtotal bar. I
> would appreciate any help.
>
>
> "OssieMac" wrote:
>
> > Hi Eric,
> >
> > I can't actually see anything wrong in the code. Try the following and
> > confirm that you have the correct ranges in the named ranges. Each time the
> > code stops, change windows to the worksheet and check that the correct range
> > is selected for the particular named range.
> >
> > The msgboxs will also confirm that the sub is being called.
> >
> > 'Subs for copying utility usage data
> > Sub PGE_Res_WriteUsage()
> >
> > 'Confirm range of named range
> > Me.Range("vPGE_Res_E1Usage").Select
> > Stop 'change window to worksheet and check selected range
> >
> > 'Confirm range of named range
> > Me.Range("vUtilityUsage_Basic").Select
> > Stop 'change window to worksheet and check selected range
> >
> > Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > End Sub
> >
> > --
> > Regards,
> >
> > OssieMac
> >
> >
> > "Eric" wrote:
> >
> > > Hello,
> > >
> > > I can't figure out what is wrong with the following code. Everything works,
> > > except for the "'subs to copy utility usage data" My goal with these subs is
> > > to copy data from one table to another. I want all changes to the data to be
> > > immediately updated. Also the data is always entered into the table (which is
> > > named and has 3 columns and 13 rows) on the left side of the equation to a
> > > table on the right side of the code . I should mention that the named table
> > > data I am copying doesn't include the table headers. I am fairly new to VBA
> > > code, can somebody tell me where I went wrong? Here is my code:
> > >
> > >
> > > Option Explicit
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > 'Hide/Unhide Rows when you change Utility Company on the phone script
> > > If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
> > > Select Case LCase(Me.Range("vUtility_Company").Value)
> > > Case LCase("")
> > > Call PS_MinimizeALL
> > > Case LCase("PGE Residential")
> > > Call PS_MinimizeALL
> > > Call PGE_Res
> > > Call PGE_Res_WriteUsage
> > > Case LCase("PGE Business")
> > > Call PS_MinimizeALL
> > > Call PGE_Bus
> > > Call PGE_Bus_WriteUsage
> > > Case LCase("SMUD Residential")
> > > Call PS_MinimizeALL
> > > Call SMUD_Res
> > > Call SMUD_Res_WriteUsage
> > > Case LCase("Debug")
> > > Call Show_All
> > > Case Else
> > > 'do nothing, just continue to the end sub
> > > End Select
> > > Else
> > > 'Hide/Unhide Rows when you change Rent/Own Value
> > > If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
> > > Select Case LCase(Me.Range("vRentOwn").Value)
> > > Case LCase("")
> > > Call PS_MinimizeRentOwn
> > > Case LCase("Rent")
> > > Call PS_MaximizeRentOwn
> > > Case LCase("Own")
> > > Call PS_MinimizeRentOwn
> > > Case Else
> > > End Select
> > > End If
> > > End If
> > >
> > > End Sub
> > > 'Subs for Utility Company
> > > Sub Show_All()
> > > Me.Rows("30:1000").EntireRow.Hidden = False
> > > End Sub
> > > Sub PS_MinimizeALL()
> > > Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
> > > End Sub
> > > Sub PGE_Res()
> > > Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
> > > End Sub
> > > Sub PGE_Bus()
> > > Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
> > > End Sub
> > > Sub SMUD_Res()
> > > Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
> > > End Sub
> > > 'Subs for Rent/Own
> > > Sub PS_MinimizeRentOwn()
> > > Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
> > > End Sub
> > > Sub PS_MaximizeRentOwn()
> > > Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
> > > End Sub
> > > 'Subs for copying utility usage data
> > > Sub PGE_Res_WriteUsage()
> > > Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > End Sub
> > > Sub PGE_Bus_WriteUsage()
> > > Me.Range("vPGE_Bus_A1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > End Sub
> > > Sub SMUD_Res_WriteUsage()
> > > Me.Range("vSMUD_Res_Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > End Sub

Hi OssieMac,

Thanks for the clearing up an oversight of mine. All I needed to do was put
in Sheet2.Range and everything came to life. Everything was great for a
second, then I realized that the code only updated when I actually clicked on
the cell named vUtility_Company. Is there a way to get everything to update
as values are changed in my table? Basically I would like it to act update
any change the moment it happens, not just when I click a value for
vUtility_Company.

In case that is confusing, I would like it to act like an equals sign in a
worksheet. For example if A2 had =C2 in it, then the moment I change a value
in A2, C2 is updated. I would like the same type update. Also I should state
that data is only being updated one way. The Range vUtilityUsage_Basic is
locked and protected so the user can't change it's value.

Here is the working code I am currently using"

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Hide/Unhide Rows when you change Utility Company on the phone script
If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
On Error GoTo ErrH:
Application.EnableEvents = False
Select Case LCase(Me.Range("vUtility_Company").Value)
Case LCase("")
Call PS_MinimizeALL
Case LCase("PGE Residential")
Call PS_MinimizeALL
Call PGE_Res
Call PGE_Res_WriteUsage
Case LCase("PGE Business")
Call PS_MinimizeALL
Call PGE_Bus
Call PGE_Bus_WriteUsage
Case LCase("SMUD Residential")
Call PS_MinimizeALL
Call SMUD_Res
Call SMUD_Res_WriteUsage
Case LCase("Modesto ID")
Call PS_MinimizeALL
Call ModestoID_Res
Case LCase("Debug")
Call Show_All
Case Else
'do nothing, just continue to the end sub
End Select
ErrH:
Application.EnableEvents = True
Else
'Hide/Unhide Rows when you change Rent/Own Value
If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
Select Case LCase(Me.Range("vRentOwn").Value)
Case LCase("")
Call PS_MinimizeRentOwn
Case LCase("Rent")
Call PS_MaximizeRentOwn
Case LCase("Own")
Call PS_MinimizeRentOwn
Case Else
End Select
End If
End If

End Sub
'Subs for Utility Company
Sub Show_All()
Me.Rows("30:1000").EntireRow.Hidden = False
End Sub
Sub PS_MinimizeALL()
Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
End Sub
Sub PGE_Res()
Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
End Sub
Sub PGE_Bus()
Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
End Sub
Sub SMUD_Res()
Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
End Sub
Sub ModestoID_Res()
Me.Range("vPS_ModestoID_Res").EntireRow.Hidden = False
End Sub
'Subs for Rent/Own
Sub PS_MinimizeRentOwn()
Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
End Sub
Sub PS_MaximizeRentOwn()
Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
End Sub
'Subs for copying utility usage data
Sub PGE_Res_WriteUsage()
Sheet11.Range("vUtilityUsage_Basic").Value =
Me.Range("vPGE_Res_E1Usage").Value
End Sub
Sub PGE_Bus_WriteUsage()
Sheet11.Range("vUtilityUsage_Basic").Value =
Me.Range("vPGE_Bus_A1Usage").Value
End Sub
Sub SMUD_Res_WriteUsage()
Sheet11.Range("vUtilityUsage_Basic").Value =
Me.Range("vSMUD_Res_Usage").Value
End Sub
Sub ModestoID_Res_WriteUsage()
Sheet11.Range("vUtilityUsage_Basic").Value =
Me.Range("vModestoID_Res_Usage").Value
End Sub

"OssieMac" wrote:

> Hi again Eric,
>
> The error suggests one of the following
> The range is not named
> Your spelling of range name is incorrect
> The named range is on another worksheet. You do realize that Me. refers to
> the worksheet on which the Change has occurred. That is the worksheet
> belonging to the sub
> Private Sub Worksheet_Change(ByVal Target As Range)
> If the range name belongs to another worksheet then reference it with the
> appropriate worksheet name as follows
> Sheets("Sheet2").Range("vUtilityUsage_Basic") or
> Sheet2.Range("vUtilityUsage_Basic")
> The latter being the CodeName which is my preference because if the user
> changes the worksheet name, the CodeName does not change. The CodeName can be
> obtained from the VBA Editor window in the Project Explorer section at the
> left. The CodeName is the one that is NOT in parenthesis. The name in
> parenthesis is the name that can be changed by the user.
>
> Of course you cannot actually select any range unless the worksheet with the
> range is the ActiveSheet so my test would require activating that worksheet
> first.
>
> However, you can use code like this
> Me.Range("vPGE_Res_E1Usage").Value = _
> Sheet2.Range("vUtilityUsage_Basic").Value
>
>
> --
> Regards,
>
> OssieMac
>
>
> "Eric" wrote:
>
> > Hello,
> >
> > It works fine for the following code:
> >
> > 'Confirm range of named range
> > Me.Range("vPGE_Res_E1Usage").Select
> > Stop 'change window to worksheet and check selected range
> >
> > However I get an error when I run the following code:
> >
> > 'Confirm range of named range
> > Me.Range("vUtilityUsage_Basic").Select
> > Stop 'change window to worksheet and check selected range
> >
> > Which says:
> > Run-time error '1004':
> > Method 'Range' of object'_Worksheet' failed
> >
> > I have made sure that the name is accurate, any ideas why this would happen?
> > I should probably also add that the second Range vUtilityUsage_Basic refers
> > only to the first three colums of a table (it has 5 total) while the first
> > range vPGE_Res_E1Usage is the whole table (it only has 3 columns total). Both
> > have the same number of rows, except the second table has a subtotal bar. I
> > would appreciate any help.
> >
> >
> > "OssieMac" wrote:
> >
> > > Hi Eric,
> > >
> > > I can't actually see anything wrong in the code. Try the following and
> > > confirm that you have the correct ranges in the named ranges. Each time the
> > > code stops, change windows to the worksheet and check that the correct range
> > > is selected for the particular named range.
> > >
> > > The msgboxs will also confirm that the sub is being called.
> > >
> > > 'Subs for copying utility usage data
> > > Sub PGE_Res_WriteUsage()
> > >
> > > 'Confirm range of named range
> > > Me.Range("vPGE_Res_E1Usage").Select
> > > Stop 'change window to worksheet and check selected range
> > >
> > > 'Confirm range of named range
> > > Me.Range("vUtilityUsage_Basic").Select
> > > Stop 'change window to worksheet and check selected range
> > >
> > > Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > End Sub
> > >
> > > --
> > > Regards,
> > >
> > > OssieMac
> > >
> > >
> > > "Eric" wrote:
> > >
> > > > Hello,
> > > >
> > > > I can't figure out what is wrong with the following code. Everything works,
> > > > except for the "'subs to copy utility usage data" My goal with these subs is
> > > > to copy data from one table to another. I want all changes to the data to be
> > > > immediately updated. Also the data is always entered into the table (which is
> > > > named and has 3 columns and 13 rows) on the left side of the equation to a
> > > > table on the right side of the code . I should mention that the named table
> > > > data I am copying doesn't include the table headers. I am fairly new to VBA
> > > > code, can somebody tell me where I went wrong? Here is my code:
> > > >
> > > >
> > > > Option Explicit
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > 'Hide/Unhide Rows when you change Utility Company on the phone script
> > > > If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
> > > > Select Case LCase(Me.Range("vUtility_Company").Value)
> > > > Case LCase("")
> > > > Call PS_MinimizeALL
> > > > Case LCase("PGE Residential")
> > > > Call PS_MinimizeALL
> > > > Call PGE_Res
> > > > Call PGE_Res_WriteUsage
> > > > Case LCase("PGE Business")
> > > > Call PS_MinimizeALL
> > > > Call PGE_Bus
> > > > Call PGE_Bus_WriteUsage
> > > > Case LCase("SMUD Residential")
> > > > Call PS_MinimizeALL
> > > > Call SMUD_Res
> > > > Call SMUD_Res_WriteUsage
> > > > Case LCase("Debug")
> > > > Call Show_All
> > > > Case Else
> > > > 'do nothing, just continue to the end sub
> > > > End Select
> > > > Else
> > > > 'Hide/Unhide Rows when you change Rent/Own Value
> > > > If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
> > > > Select Case LCase(Me.Range("vRentOwn").Value)
> > > > Case LCase("")
> > > > Call PS_MinimizeRentOwn
> > > > Case LCase("Rent")
> > > > Call PS_MaximizeRentOwn
> > > > Case LCase("Own")
> > > > Call PS_MinimizeRentOwn
> > > > Case Else
> > > > End Select
> > > > End If
> > > > End If
> > > >
> > > > End Sub
> > > > 'Subs for Utility Company
> > > > Sub Show_All()
> > > > Me.Rows("30:1000").EntireRow.Hidden = False
> > > > End Sub
> > > > Sub PS_MinimizeALL()
> > > > Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
> > > > End Sub
> > > > Sub PGE_Res()
> > > > Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
> > > > End Sub
> > > > Sub PGE_Bus()
> > > > Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
> > > > End Sub
> > > > Sub SMUD_Res()
> > > > Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
> > > > End Sub
> > > > 'Subs for Rent/Own
> > > > Sub PS_MinimizeRentOwn()
> > > > Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
> > > > End Sub
> > > > Sub PS_MaximizeRentOwn()
> > > > Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
> > > > End Sub
> > > > 'Subs for copying utility usage data
> > > > Sub PGE_Res_WriteUsage()
> > > > Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > > End Sub
> > > > Sub PGE_Bus_WriteUsage()
> > > > Me.Range("vPGE_Bus_A1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > > End Sub
> > > > Sub SMUD_Res_WriteUsage()
> > > > Me.Range("vSMUD_Res_Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > > End Sub

Hi Eric,

The following line of code determines what cells need to be changed before
the code will run.

If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then

If Not ..... Is Nothing means that it is something. Therefore change took
place within the range.

Actually the sub is called every time you make any change on the worksheet
but the If statement causes it to go the line following End If when the
changed cell (or cells) do not intersect with Me.Range("vUtility_Company").
The intersect means if at least one of the cells being changed is within the
range. For example you insert a row which runs through the middle of it, the
code runs because the change intersects with the range.

If you remove the If / End If statements, the code will run every time any
change is made anywhere on the worksheet. Perhaps you don't want to go that
far but think about expanding the range to include all cells that you are
likely to change and want the code to run when changed. You don't have to use
a named range you could use something like the following

If Not (Intersect(Target, Me.Range("A1:H1000")) Is Nothing) Then


--
Regards,

OssieMac


"Eric" wrote:

> Hi OssieMac,
>
> Thanks for the clearing up an oversight of mine. All I needed to do was put
> in Sheet2.Range and everything came to life. Everything was great for a
> second, then I realized that the code only updated when I actually clicked on
> the cell named vUtility_Company. Is there a way to get everything to update
> as values are changed in my table? Basically I would like it to act update
> any change the moment it happens, not just when I click a value for
> vUtility_Company.
>
> In case that is confusing, I would like it to act like an equals sign in a
> worksheet. For example if A2 had =C2 in it, then the moment I change a value
> in A2, C2 is updated. I would like the same type update. Also I should state
> that data is only being updated one way. The Range vUtilityUsage_Basic is
> locked and protected so the user can't change it's value.
>
> Here is the working code I am currently using"
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> 'Hide/Unhide Rows when you change Utility Company on the phone script
> If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
> On Error GoTo ErrH:
> Application.EnableEvents = False
> Select Case LCase(Me.Range("vUtility_Company").Value)
> Case LCase("")
> Call PS_MinimizeALL
> Case LCase("PGE Residential")
> Call PS_MinimizeALL
> Call PGE_Res
> Call PGE_Res_WriteUsage
> Case LCase("PGE Business")
> Call PS_MinimizeALL
> Call PGE_Bus
> Call PGE_Bus_WriteUsage
> Case LCase("SMUD Residential")
> Call PS_MinimizeALL
> Call SMUD_Res
> Call SMUD_Res_WriteUsage
> Case LCase("Modesto ID")
> Call PS_MinimizeALL
> Call ModestoID_Res
> Case LCase("Debug")
> Call Show_All
> Case Else
> 'do nothing, just continue to the end sub
> End Select
> ErrH:
> Application.EnableEvents = True
> Else
> 'Hide/Unhide Rows when you change Rent/Own Value
> If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
> Select Case LCase(Me.Range("vRentOwn").Value)
> Case LCase("")
> Call PS_MinimizeRentOwn
> Case LCase("Rent")
> Call PS_MaximizeRentOwn
> Case LCase("Own")
> Call PS_MinimizeRentOwn
> Case Else
> End Select
> End If
> End If
>
> End Sub
> 'Subs for Utility Company
> Sub Show_All()
> Me.Rows("30:1000").EntireRow.Hidden = False
> End Sub
> Sub PS_MinimizeALL()
> Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
> End Sub
> Sub PGE_Res()
> Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
> End Sub
> Sub PGE_Bus()
> Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
> End Sub
> Sub SMUD_Res()
> Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
> End Sub
> Sub ModestoID_Res()
> Me.Range("vPS_ModestoID_Res").EntireRow.Hidden = False
> End Sub
> 'Subs for Rent/Own
> Sub PS_MinimizeRentOwn()
> Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
> End Sub
> Sub PS_MaximizeRentOwn()
> Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
> End Sub
> 'Subs for copying utility usage data
> Sub PGE_Res_WriteUsage()
> Sheet11.Range("vUtilityUsage_Basic").Value =
> Me.Range("vPGE_Res_E1Usage").Value
> End Sub
> Sub PGE_Bus_WriteUsage()
> Sheet11.Range("vUtilityUsage_Basic").Value =
> Me.Range("vPGE_Bus_A1Usage").Value
> End Sub
> Sub SMUD_Res_WriteUsage()
> Sheet11.Range("vUtilityUsage_Basic").Value =
> Me.Range("vSMUD_Res_Usage").Value
> End Sub
> Sub ModestoID_Res_WriteUsage()
> Sheet11.Range("vUtilityUsage_Basic").Value =
> Me.Range("vModestoID_Res_Usage").Value
> End Sub
>
> "OssieMac" wrote:
>
> > Hi again Eric,
> >
> > The error suggests one of the following
> > The range is not named
> > Your spelling of range name is incorrect
> > The named range is on another worksheet. You do realize that Me. refers to
> > the worksheet on which the Change has occurred. That is the worksheet
> > belonging to the sub
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If the range name belongs to another worksheet then reference it with the
> > appropriate worksheet name as follows
> > Sheets("Sheet2").Range("vUtilityUsage_Basic") or
> > Sheet2.Range("vUtilityUsage_Basic")
> > The latter being the CodeName which is my preference because if the user
> > changes the worksheet name, the CodeName does not change. The CodeName can be
> > obtained from the VBA Editor window in the Project Explorer section at the
> > left. The CodeName is the one that is NOT in parenthesis. The name in
> > parenthesis is the name that can be changed by the user.
> >
> > Of course you cannot actually select any range unless the worksheet with the
> > range is the ActiveSheet so my test would require activating that worksheet
> > first.
> >
> > However, you can use code like this
> > Me.Range("vPGE_Res_E1Usage").Value = _
> > Sheet2.Range("vUtilityUsage_Basic").Value
> >
> >
> > --
> > Regards,
> >
> > OssieMac
> >
> >
> > "Eric" wrote:
> >
> > > Hello,
> > >
> > > It works fine for the following code:
> > >
> > > 'Confirm range of named range
> > > Me.Range("vPGE_Res_E1Usage").Select
> > > Stop 'change window to worksheet and check selected range
> > >
> > > However I get an error when I run the following code:
> > >
> > > 'Confirm range of named range
> > > Me.Range("vUtilityUsage_Basic").Select
> > > Stop 'change window to worksheet and check selected range
> > >
> > > Which says:
> > > Run-time error '1004':
> > > Method 'Range' of object'_Worksheet' failed
> > >
> > > I have made sure that the name is accurate, any ideas why this would happen?
> > > I should probably also add that the second Range vUtilityUsage_Basic refers
> > > only to the first three colums of a table (it has 5 total) while the first
> > > range vPGE_Res_E1Usage is the whole table (it only has 3 columns total). Both
> > > have the same number of rows, except the second table has a subtotal bar. I
> > > would appreciate any help.
> > >
> > >
> > > "OssieMac" wrote:
> > >
> > > > Hi Eric,
> > > >
> > > > I can't actually see anything wrong in the code. Try the following and
> > > > confirm that you have the correct ranges in the named ranges. Each time the
> > > > code stops, change windows to the worksheet and check that the correct range
> > > > is selected for the particular named range.
> > > >
> > > > The msgboxs will also confirm that the sub is being called.
> > > >
> > > > 'Subs for copying utility usage data
> > > > Sub PGE_Res_WriteUsage()
> > > >
> > > > 'Confirm range of named range
> > > > Me.Range("vPGE_Res_E1Usage").Select
> > > > Stop 'change window to worksheet and check selected range
> > > >
> > > > 'Confirm range of named range
> > > > Me.Range("vUtilityUsage_Basic").Select
> > > > Stop 'change window to worksheet and check selected range
> > > >
> > > > Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > > End Sub
> > > >
> > > > --
> > > > Regards,
> > > >
> > > > OssieMac
> > > >
> > > >
> > > > "Eric" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I can't figure out what is wrong with the following code. Everything works,
> > > > > except for the "'subs to copy utility usage data" My goal with these subs is
> > > > > to copy data from one table to another. I want all changes to the data to be
> > > > > immediately updated. Also the data is always entered into the table (which is
> > > > > named and has 3 columns and 13 rows) on the left side of the equation to a
> > > > > table on the right side of the code . I should mention that the named table
> > > > > data I am copying doesn't include the table headers. I am fairly new to VBA
> > > > > code, can somebody tell me where I went wrong? Here is my code:
> > > > >
> > > > >
> > > > > Option Explicit
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > 'Hide/Unhide Rows when you change Utility Company on the phone script
> > > > > If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
> > > > > Select Case LCase(Me.Range("vUtility_Company").Value)
> > > > > Case LCase("")
> > > > > Call PS_MinimizeALL
> > > > > Case LCase("PGE Residential")
> > > > > Call PS_MinimizeALL
> > > > > Call PGE_Res
> > > > > Call PGE_Res_WriteUsage
> > > > > Case LCase("PGE Business")
> > > > > Call PS_MinimizeALL
> > > > > Call PGE_Bus
> > > > > Call PGE_Bus_WriteUsage
> > > > > Case LCase("SMUD Residential")
> > > > > Call PS_MinimizeALL
> > > > > Call SMUD_Res
> > > > > Call SMUD_Res_WriteUsage
> > > > > Case LCase("Debug")
> > > > > Call Show_All
> > > > > Case Else
> > > > > 'do nothing, just continue to the end sub
> > > > > End Select
> > > > > Else
> > > > > 'Hide/Unhide Rows when you change Rent/Own Value
> > > > > If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
> > > > > Select Case LCase(Me.Range("vRentOwn").Value)
> > > > > Case LCase("")
> > > > > Call PS_MinimizeRentOwn
> > > > > Case LCase("Rent")
> > > > > Call PS_MaximizeRentOwn
> > > > > Case LCase("Own")
> > > > > Call PS_MinimizeRentOwn
> > > > > Case Else
> > > > > End Select
> > > > > End If
> > > > > End If
> > > > >
> > > > > End Sub
> > > > > 'Subs for Utility Company
> > > > > Sub Show_All()
> > > > > Me.Rows("30:1000").EntireRow.Hidden = False
> > > > > End Sub
> > > > > Sub PS_MinimizeALL()
> > > > > Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
> > > > > End Sub
> > > > > Sub PGE_Res()
> > > > > Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
> > > > > End Sub
> > > > > Sub PGE_Bus()
> > > > > Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
> > > > > End Sub
> > > > > Sub SMUD_Res()
> > > > > Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
> > > > > End Sub
> > > > > 'Subs for Rent/Own
> > > > > Sub PS_MinimizeRentOwn()
> > > > > Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
> > > > > End Sub
> > > > > Sub PS_MaximizeRentOwn()
> > > > > Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
> > > > > End Sub
> > > > > 'Subs for copying utility usage data
> > > > > Sub PGE_Res_WriteUsage()
> > > > > Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > > > End Sub
> > > > > Sub PGE_Bus_WriteUsage()
> > > > > Me.Range("vPGE_Bus_A1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > > > End Sub
> > > > > Sub SMUD_Res_WriteUsage()
> > > > > Me.Range("vSMUD_Res_Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > > > End Sub

Thank You OssieMac! Everything now works great!

"OssieMac" wrote:

> Hi Eric,
>
> The following line of code determines what cells need to be changed before
> the code will run.
>
> If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
>
> If Not ..... Is Nothing means that it is something. Therefore change took
> place within the range.
>
> Actually the sub is called every time you make any change on the worksheet
> but the If statement causes it to go the line following End If when the
> changed cell (or cells) do not intersect with Me.Range("vUtility_Company").
> The intersect means if at least one of the cells being changed is within the
> range. For example you insert a row which runs through the middle of it, the
> code runs because the change intersects with the range.
>
> If you remove the If / End If statements, the code will run every time any
> change is made anywhere on the worksheet. Perhaps you don't want to go that
> far but think about expanding the range to include all cells that you are
> likely to change and want the code to run when changed. You don't have to use
> a named range you could use something like the following
>
> If Not (Intersect(Target, Me.Range("A1:H1000")) Is Nothing) Then
>
>
> --
> Regards,
>
> OssieMac
>
>
> "Eric" wrote:
>
> > Hi OssieMac,
> >
> > Thanks for the clearing up an oversight of mine. All I needed to do was put
> > in Sheet2.Range and everything came to life. Everything was great for a
> > second, then I realized that the code only updated when I actually clicked on
> > the cell named vUtility_Company. Is there a way to get everything to update
> > as values are changed in my table? Basically I would like it to act update
> > any change the moment it happens, not just when I click a value for
> > vUtility_Company.
> >
> > In case that is confusing, I would like it to act like an equals sign in a
> > worksheet. For example if A2 had =C2 in it, then the moment I change a value
> > in A2, C2 is updated. I would like the same type update. Also I should state
> > that data is only being updated one way. The Range vUtilityUsage_Basic is
> > locked and protected so the user can't change it's value.
> >
> > Here is the working code I am currently using"
> >
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > 'Hide/Unhide Rows when you change Utility Company on the phone script
> > If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
> > On Error GoTo ErrH:
> > Application.EnableEvents = False
> > Select Case LCase(Me.Range("vUtility_Company").Value)
> > Case LCase("")
> > Call PS_MinimizeALL
> > Case LCase("PGE Residential")
> > Call PS_MinimizeALL
> > Call PGE_Res
> > Call PGE_Res_WriteUsage
> > Case LCase("PGE Business")
> > Call PS_MinimizeALL
> > Call PGE_Bus
> > Call PGE_Bus_WriteUsage
> > Case LCase("SMUD Residential")
> > Call PS_MinimizeALL
> > Call SMUD_Res
> > Call SMUD_Res_WriteUsage
> > Case LCase("Modesto ID")
> > Call PS_MinimizeALL
> > Call ModestoID_Res
> > Case LCase("Debug")
> > Call Show_All
> > Case Else
> > 'do nothing, just continue to the end sub
> > End Select
> > ErrH:
> > Application.EnableEvents = True
> > Else
> > 'Hide/Unhide Rows when you change Rent/Own Value
> > If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
> > Select Case LCase(Me.Range("vRentOwn").Value)
> > Case LCase("")
> > Call PS_MinimizeRentOwn
> > Case LCase("Rent")
> > Call PS_MaximizeRentOwn
> > Case LCase("Own")
> > Call PS_MinimizeRentOwn
> > Case Else
> > End Select
> > End If
> > End If
> >
> > End Sub
> > 'Subs for Utility Company
> > Sub Show_All()
> > Me.Rows("30:1000").EntireRow.Hidden = False
> > End Sub
> > Sub PS_MinimizeALL()
> > Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
> > End Sub
> > Sub PGE_Res()
> > Me.Range("vPS_PGE_Res").EntireRow.Hidden = False
> > End Sub
> > Sub PGE_Bus()
> > Me.Range("vPS_PGE_Bus").EntireRow.Hidden = False
> > End Sub
> > Sub SMUD_Res()
> > Me.Range("vPS_SMUD_Res").EntireRow.Hidden = False
> > End Sub
> > Sub ModestoID_Res()
> > Me.Range("vPS_ModestoID_Res").EntireRow.Hidden = False
> > End Sub
> > 'Subs for Rent/Own
> > Sub PS_MinimizeRentOwn()
> > Me.Range("vRentOwn_Rows").EntireRow.Hidden = True
> > End Sub
> > Sub PS_MaximizeRentOwn()
> > Me.Range("vRentOwn_Rows").EntireRow.Hidden = False
> > End Sub
> > 'Subs for copying utility usage data
> > Sub PGE_Res_WriteUsage()
> > Sheet11.Range("vUtilityUsage_Basic").Value =
> > Me.Range("vPGE_Res_E1Usage").Value
> > End Sub
> > Sub PGE_Bus_WriteUsage()
> > Sheet11.Range("vUtilityUsage_Basic").Value =
> > Me.Range("vPGE_Bus_A1Usage").Value
> > End Sub
> > Sub SMUD_Res_WriteUsage()
> > Sheet11.Range("vUtilityUsage_Basic").Value =
> > Me.Range("vSMUD_Res_Usage").Value
> > End Sub
> > Sub ModestoID_Res_WriteUsage()
> > Sheet11.Range("vUtilityUsage_Basic").Value =
> > Me.Range("vModestoID_Res_Usage").Value
> > End Sub
> >
> > "OssieMac" wrote:
> >
> > > Hi again Eric,
> > >
> > > The error suggests one of the following
> > > The range is not named
> > > Your spelling of range name is incorrect
> > > The named range is on another worksheet. You do realize that Me. refers to
> > > the worksheet on which the Change has occurred. That is the worksheet
> > > belonging to the sub
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If the range name belongs to another worksheet then reference it with the
> > > appropriate worksheet name as follows
> > > Sheets("Sheet2").Range("vUtilityUsage_Basic") or
> > > Sheet2.Range("vUtilityUsage_Basic")
> > > The latter being the CodeName which is my preference because if the user
> > > changes the worksheet name, the CodeName does not change. The CodeName can be
> > > obtained from the VBA Editor window in the Project Explorer section at the
> > > left. The CodeName is the one that is NOT in parenthesis. The name in
> > > parenthesis is the name that can be changed by the user.
> > >
> > > Of course you cannot actually select any range unless the worksheet with the
> > > range is the ActiveSheet so my test would require activating that worksheet
> > > first.
> > >
> > > However, you can use code like this
> > > Me.Range("vPGE_Res_E1Usage").Value = _
> > > Sheet2.Range("vUtilityUsage_Basic").Value
> > >
> > >
> > > --
> > > Regards,
> > >
> > > OssieMac
> > >
> > >
> > > "Eric" wrote:
> > >
> > > > Hello,
> > > >
> > > > It works fine for the following code:
> > > >
> > > > 'Confirm range of named range
> > > > Me.Range("vPGE_Res_E1Usage").Select
> > > > Stop 'change window to worksheet and check selected range
> > > >
> > > > However I get an error when I run the following code:
> > > >
> > > > 'Confirm range of named range
> > > > Me.Range("vUtilityUsage_Basic").Select
> > > > Stop 'change window to worksheet and check selected range
> > > >
> > > > Which says:
> > > > Run-time error '1004':
> > > > Method 'Range' of object'_Worksheet' failed
> > > >
> > > > I have made sure that the name is accurate, any ideas why this would happen?
> > > > I should probably also add that the second Range vUtilityUsage_Basic refers
> > > > only to the first three colums of a table (it has 5 total) while the first
> > > > range vPGE_Res_E1Usage is the whole table (it only has 3 columns total). Both
> > > > have the same number of rows, except the second table has a subtotal bar. I
> > > > would appreciate any help.
> > > >
> > > >
> > > > "OssieMac" wrote:
> > > >
> > > > > Hi Eric,
> > > > >
> > > > > I can't actually see anything wrong in the code. Try the following and
> > > > > confirm that you have the correct ranges in the named ranges. Each time the
> > > > > code stops, change windows to the worksheet and check that the correct range
> > > > > is selected for the particular named range.
> > > > >
> > > > > The msgboxs will also confirm that the sub is being called.
> > > > >
> > > > > 'Subs for copying utility usage data
> > > > > Sub PGE_Res_WriteUsage()
> > > > >
> > > > > 'Confirm range of named range
> > > > > Me.Range("vPGE_Res_E1Usage").Select
> > > > > Stop 'change window to worksheet and check selected range
> > > > >
> > > > > 'Confirm range of named range
> > > > > Me.Range("vUtilityUsage_Basic").Select
> > > > > Stop 'change window to worksheet and check selected range
> > > > >
> > > > > Me.Range("vPGE_Res_E1Usage").Value = Me.Range("vUtilityUsage_Basic").Value
> > > > > End Sub
> > > > >
> > > > > --
> > > > > Regards,
> > > > >
> > > > > OssieMac
> > > > >
> > > > >
> > > > > "Eric" wrote:
> > > > >
> > > > > > Hello,
> > > > > >
> > > > > > I can't figure out what is wrong with the following code. Everything works,
> > > > > > except for the "'subs to copy utility usage data" My goal with these subs is
> > > > > > to copy data from one table to another. I want all changes to the data to be
> > > > > > immediately updated. Also the data is always entered into the table (which is
> > > > > > named and has 3 columns and 13 rows) on the left side of the equation to a
> > > > > > table on the right side of the code . I should mention that the named table
> > > > > > data I am copying doesn't include the table headers. I am fairly new to VBA
> > > > > > code, can somebody tell me where I went wrong? Here is my code:
> > > > > >
> > > > > >
> > > > > > Option Explicit
> > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > 'Hide/Unhide Rows when you change Utility Company on the phone script
> > > > > > If Not (Intersect(Target, Me.Range("vUtility_Company")) Is Nothing) Then
> > > > > > Select Case LCase(Me.Range("vUtility_Company").Value)
> > > > > > Case LCase("")
> > > > > > Call PS_MinimizeALL
> > > > > > Case LCase("PGE Residential")
> > > > > > Call PS_MinimizeALL
> > > > > > Call PGE_Res
> > > > > > Call PGE_Res_WriteUsage
> > > > > > Case LCase("PGE Business")
> > > > > > Call PS_MinimizeALL
> > > > > > Call PGE_Bus
> > > > > > Call PGE_Bus_WriteUsage
> > > > > > Case LCase("SMUD Residential")
> > > > > > Call PS_MinimizeALL
> > > > > > Call SMUD_Res
> > > > > > Call SMUD_Res_WriteUsage
> > > > > > Case LCase("Debug")
> > > > > > Call Show_All
> > > > > > Case Else
> > > > > > 'do nothing, just continue to the end sub
> > > > > > End Select
> > > > > > Else
> > > > > > 'Hide/Unhide Rows when you change Rent/Own Value
> > > > > > If Not (Intersect(Target, Me.Range("vRentOwn")) Is Nothing) Then
> > > > > > Select Case LCase(Me.Range("vRentOwn").Value)
> > > > > > Case LCase("")
> > > > > > Call PS_MinimizeRentOwn
> > > > > > Case LCase("Rent")
> > > > > > Call PS_MaximizeRentOwn
> > > > > > Case LCase("Own")
> > > > > > Call PS_MinimizeRentOwn
> > > > > > Case Else
> > > > > > End Select
> > > > > > End If
> > > > > > End If
> > > > > >
> > > > > > End Sub
> > > > > > 'Subs for Utility Company
> > > > > > Sub Show_All()
> > > > > > Me.Rows("30:1000").EntireRow.Hidden = False
> > > > > > End Sub
> > > > > > Sub PS_MinimizeALL()
> > > > > > Me.Range("vPS_MinAll_Utilities").EntireRow.Hidden = True
> > > > > > End Sub
> > > > > > Sub PGE_Res()