Code error to mirror data from one table to another

E

Eric

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
 
O

OssieMac

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
 
E

Eric

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.
 
O

OssieMac

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
 
E

Eric

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
 
O

OssieMac

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
 
E

Eric

Thank You OssieMac! Everything now works great!

OssieMac said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mirror value to another cell. 2
mirror table depending on cell value 0
Data Validation List Hide Unhide Rows 5
Validation code not working with pasted data 0
Somewhat complex code to copy data from one sheet to another 9
Data validation to control a pivot table field 0
Detecting Duplicate Sums - Worksheet code 0
Copy data from another workbook to any open workbook 3

Top