How to call/run "Private Sub Worksheet_Change(ByVal Target As Range)"

fahadalambd

New Member
Joined
Sep 16, 2022
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone, I am new in VBA Macro Scripting.

I am trying to sync data between two sheets ("Sheet1" and "Sheet2") in the same excel file.

I found the solution from this website (How to synchronize data between two sheets or ranges in Excel - VBA and VB.Net Tutorials, Education and Programming Services). and this is the code -

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sourceSheet As Worksheet, targetSheet As Worksheet
Dim syncRange As String
Dim isInRange
 
'Set the source and target sheets here
Set sourceSheet = Sheet1
Set targetSheet = sheet2
 
'This will be the column that needs to be synced
syncRange = "A1:C8"
 
'Check if the modified cell lies within the range to be synced
Set isInRange = Application.Intersect(Target, Range(syncRange))
 
If isInRange Is Nothing Then
'Do nothing if the cell falls outside the range
Else
'Else sync the cell contents
targetSheet.Range(Target.Address) = sourceSheet.Range(Target.Address)
End If
 
End Sub

Problem is that I can't call this procedure in a Module. Please some one help to solve this problem. Here is the screen-shot -
 

Attachments

  • Capture.PNG
    Capture.PNG
    8.3 KB · Views: 13
  • 2.PNG
    2.PNG
    37.8 KB · Views: 15

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,478
Office Version
  1. 2010
Platform
  1. Windows
worksheet event code goes in the sheet module and is triggered automatically.
The page you link to says
As mentioned above, please note that the code should be written in the code module of Sheet1. Here is the code.

double click on Sheet1 on the left side there and the sheet module will open
 
Upvote 0
Solution

fahadalambd

New Member
Joined
Sep 16, 2022
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
worksheet event code goes in the sheet module and is triggered automatically.
The page you link to says


double click on Sheet1 on the left side there and the sheet module will open
Thanks buddy, it is working now. :)
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,493
Office Version
  1. 365
Platform
  1. Windows
Thanks buddy, it is working now. :)
The marked solution has been changed accordingly.

@fahadalambd: In your future questions, please mark the post as the solution that answered your question instead of your feedback message as it will help future readers. No further action is required for this thread.
 
Upvote 0

Forum statistics

Threads
1,186,426
Messages
5,957,765
Members
438,319
Latest member
Girlboss3036

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top