Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error to mirror data from one table to another
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error to mirror data from one table to another
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error to mirror data from one table to another
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error to mirror data from one table to another
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error to mirror data from one table to another
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code error to mirror data from one table to another
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() |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for an excel function which can mirror opposite the data | New Users to Excel | |||
synchronizing mutiple sheets to mirror the previous data | Excel Discussion (Misc queries) | |||
mirror table depending on cell value | Excel Programming | |||
Duplicating/Mirror Data into another worksheet within same workboo | Excel Worksheet Functions | |||
How do you mirror data and formatting in Excel '03 | Excel Worksheet Functions |