-2

Sheet 1 as ENTER VALUE

enter image description here

Sheet 2 as MY VALUE and DATA

enter image description here

Sheet value is referenced from Sheet1 Value using =Sheet1!E4

I want to avoid onclick Button event => as soon as value in sheet 1 changes to 10 then My value in sheet 2 changes to 10 => then taking the value which is changed in sheet 2 the macros should automatically detect the value change as occured then print of Data should happen that many times in column I

Module 1 my macros :

Sub mac()

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

Set ws = ActiveWorkbook.ActiveSheet
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 2 : Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E4"), Range(Target.Address)) Is Nothing Then
Call mac
End Sub

output : should be like this below Output Image: https://i.sstatic.net/voz7g.png

Improve this question
3
  • You've added quite a lot of detail, but not quite clear what you think is not working...? Commented May 25, 2019 at 8:30
  • You probably want to use that Worksheet_Change event in Sheet1 instead? And set your worksheet by name in your function Set ws = ActiveWorkbook.Sheets("Sheet2") Commented May 25, 2019 at 8:40
  • Possible duplicate of VBA trigger macro on cell value change Commented May 26, 2019 at 8:04

1 Answer 1

Reset to default
2

Few things

  • Your Worksheet_Change procedure should be in Sheet1 code instead of Sheet2 - least from the way you phrased your question.

  • Also be explicit with your Worksheet declarations (eg. Sheets("Sheet1")), instead of using things like ActiveSheet

  • In your Intersect, notation of Range(Target.Address) is completely redundant, as Target already is a Range object.

Your code in Sheet1 should be:

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

And In Module

Private Sub mac()

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

Set ws = ThisWorkbook.Sheets("Sheet2") ' note the explicit declaration
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

And as a personal note, I would beware of using "single-line If syntax" in my opinion it is a bad programming practice and leads to unexpected mistakes and worse code readability.

As a good example as to why, check this question/answer

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Then you have something wrong with your macro procedure, I can't read your mind, but my answer is programatically correct
Thanks Bro Working !!! Little Bit changes i made Sheet 1 Code it should be F5 not sheet 2 Value 'E4' => We need to pass value of sheet 1
Well glad it helped.
is it possible to take two values and print parallel like this => paste.pics/3eb581ed87184d251d910328537f2973 => it would be really helpful for me
create a separate question for that
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.