How do i Trigger macros automatcially for sheet 2 when value change occur in sheet 1

[jackbhai]

I have Sheet 1 and Sheet
Sheet 1 => https://paste.pics/6d48d50d54592eb1bdcb31e727b44958
Sheet 2 => https://paste.pics/80f4230bf6819a80ed36e1b6415ece08
[h=2]Value of Sheet 1 => F5 => '50' => is referenced in Sheet 2 => E4 => '50'[/h]
[h=3]When i enter Value in Sheet 1 in place of F5 => Value gets Change in Sheet 2 => E4 => Automatically[/h]
[h=5]Now the issue is [/h][h=5]I want to print the DATA 1 and DATA 2 of Sheet 2 => That many times => how much value which is shown in place of Sheet 2 => E4 [Position] => in place of COLUMN => 'I' and 'J'[/h]
Like this Below Output :
https://paste.pics/494c856d2908a83ca031ee20bb706a09

My code which partially which print only one Column output but need to PRINT 'RAM' and 'RAJ' => COLUMN => 'I' and 'J'

Module :
Sub mac()
Dim ws As Worksheet
Dim rDest As Range
Dim lCount As Long
Dim sValue As String
Set ws = ThisWorkbook.Sheets("Sheet2")
Set rDest = ws.Range("I2")
With ws.Range(rDest, ws.Cells(ws.Rows.Count, rDest.Column).End(xlUp))
If .Row >= rDest.Row Then .ClearContents
End With
lCount = Val(ws.Range("E4").Value)
sValue = ws.Range("E8").Value
If lCount > 0 Then rDest.Resize(lCount) = sValue
End Sub

Code for Sheet 1 : to enable call => of macros => mac
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Sheets("Sheet1").Range("F5"), Target) Is Nothing Then
Call mac
End If
End Sub

 

[mumps]

It's difficult to work with pictures. Can you post a link to your file?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "F5" Then
      With Sheets("sheet2")
         .Range("I2", .Range("I" & Rows.Count).End(xlUp).Offset(, 1)).ClearContents
         .Range("I2").Resize(1 * Target, 1).Value = .Range("E8").Value
         .Range("J2").Resize(1 * Target, 1).Value = .Range("E12").Value
      End With
   End If
End Sub

 

[jackbhai]

@Fluff this is the Link => of my excel 100% safe Google Drive => https://drive.google.com/file/d/1D5GblPrcnaGQqKUwhj5YIthhgQxkHldl/view?usp=sharing

Can you look at the issue i am getting below is working code but as some issue data gets append but if the entered value is less than count => the below code does not work => i need to add some clearing function


I have a Excel


Sheet 1 => https://paste.pics/6d48d50d54592eb1bdcb31e727b44958


Sheet 2 => https://paste.pics/80f4230bf6819a80ed36e1b6415ece08


Value of Sheet 1 => F5 => '50' => is referenced in Sheet 2 => E4 => '50'


When i enter Value in Sheet 1 in place of F5 => Value gets Change in Sheet 2 => E4 => Automatically


using below code the DATA is Printed that many times My code


Module Code:


Sub mac
Dim ws As Worksheet
Dim rDest As Range
Dim lCount As Long
Dim sValue As String


Set ws = ThisWorkbook.Sheets("Sheet2")
Set rDest = ws.Range("I2")


With ws.Range(rDest, ws.Cells(ws.Rows.Count, rDest.Column).End(xlUp))
If .Row >= rDest.Row Then .ClearContents
End With


lCount = Val(ws.Range("E4").Value)
sValue = ws.Range("E8").Value


If lCount > 0 Then rDest.Resize(lCount) = sValue


End Sub




Sheet 1 Code:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Sheets("Sheet1").Range("F5"), Target) Is Nothing Then
Call mac
End If
End Sub




This is the Output


Like this Below Output :


https://paste.pics/494c856d2908a83ca031ee20bb706a09


My Above code Works Perfectly according to above mentioned output But as Some Flaws in it


Assume


Condition 1:


if the value entered is '50' => then data in the Column "I" and "J" gets printed 50 times => right [Perfect]


Condition 2:


if the value entered is '60' => then data in the Coulmn "I" and "J" gets appends with the previous data which it as "50" => 50 + 10 => 60 => this is right [perfect]


Condition 3:


Assume the Column "I:J" already as DATA => upto the Range '60' if the entered value is less then '60' like '30' then updation should happen only 30 times in Column "I:J" the Data should be Printed But Nothing is happening appending happens but clearing data does not happen if range is lesser then old value


There as to be some mechanism like


if Count is 50 => print => I,J


if Count is more then 50 => append I,J


if Count is less then 50 => clear column I,J and then => fill the Column I ,J


How do i achieve this mechanism

 

[Fluff]

Did you actually try my code?
It already clears the contents on sheet2

 

[jackbhai]

i need one more thing are you able to get it for me using your above code which you have posted

it works perfectly as required

But i have multiple sheets => how do i achieve it for multiple sheets => each sheet have the DATA 1 and DATA 2 values => Different

taking that values the data should be printed

each sheet as DATA 1 and DATA 2 which is different from another sheet => but the Count is Same for all

what we enter in Sheet 1 => F5

link below safe 100% google drive link => look at the output which is their in the sheet
you will come to know how i need it

https://drive.google.com/file/d/1OtAzSSS1aMevIZnhM4CprlJqzPkKXPMD/view?usp=sharing

Thanks for you help

help me out with this above issue => even the Headers are getting clears when your code is running => headers should not get cleared

 

[jackbhai]

Did you actually try my code?
It already clears the contents on sheet2

=> it is working perfect

i need one more thing are you able to get it for me using your above code which you have posted

it works perfectly as required

But i have multiple sheets => how do i achieve it for multiple sheets => each sheet have the DATA 1 and DATA 2 values => Different

taking that values the data should be printed

each sheet as DATA 1 and DATA 2 which is different from another sheet => but the Count is Same for all

what we enter in Sheet 1 => F5

link below safe 100% google drive link => look at the output which is their in the sheet
you will come to know how i need it

https://drive.google.com/file/d/1OtA...ew?usp=sharing

Thanks for you help

help me out with this above issue => even the Headers are getting clears when your code is running => headers should not get cleared

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ws As Worksheet
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "F5" Then
      For Each Ws In Sheets(Array("Sheet2", "Sheet3", "Sheet4"))
         Ws.Range("I2", Ws.Range("I" & Rows.Count).End(xlUp).Offset(, 1)).ClearContents
         Ws.Range("I2").Resize(1 * Target, 1).Value = Ws.Range("E8").Value
         Ws.Range("J2").Resize(1 * Target, 1).Value = Ws.Range("E12").Value
      Next Ws
   End If
End Sub

 

[jackbhai]

wounderful Bro !! you really made my day since morning i am trying this method But you got it | THANKS MAN |

like a Stupid i was pasting the same code again and again by changing the sheet name

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("sheet2")
.Range("I2", .Range("I" & Rows.Count).End(xlUp).Offset(, 1)).ClearContents
.Range("I2").Resize(1 * Target, 1).Value = .Range("E8").Value
.Range("J2").Resize(1 * Target, 1).Value = .Range("E12").Value
End With


With Sheets("sheet3")
.Range("I2", .Range("I" & Rows.Count).End(xlUp).Offset(, 1)).ClearContents
.Range("I2").Resize(1 * Target, 1).Value = .Range("E8").Value
.Range("J2").Resize(1 * Target, 1).Value = .Range("E12").Value
End With


With Sheets("sheet4")
.Range("I2", .Range("I" & Rows.Count).End(xlUp).Offset(, 1)).ClearContents
.Range("I2").Resize(1 * Target, 1).Value = .Range("E8").Value
.Range("J2").Resize(1 * Target, 1).Value = .Range("E12").Value
End With
End Sub

 

[Fluff]

You're welcome & thanks for the feedback