Click here to Skip to main content
14,242,144 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a Excel

It as Two Sheets => Sheet 1 and Sheet 2

Sheet 1 as => https://paste.pics/86a20854fa13010dd52c9a475baa5570

Sheet 2 as =>https://paste.pics/e361556dfc6973e1b59c8382e2065db3

The Sheet 1 as Value => 13 which is Referenced in Sheet 2

"If you see when i enter value in sheet 1 the the value gets in Sheet 2 Automatically using =Sheet1!F5

In Sheet 2 there is another thing mentioned is the Data => 'RAM'

Taking the Value of Sheet 2 => MY VALUE = 13 and DATA ='RAM'

IN COLUMN "I" of Sheet 2

under location => COLUMN => I => COPY => "I2" onwards => that many times the RAM should be printed automatically => macros should be automatically triggered

Output Should be like this : https://paste.pics/05793509907ae4e409acbb15976f7929

In case when ever i change the value in Sheet 1 => VALUE => PASSED TO Sheet 2 => Sheet 2 value should be taken as Count and Print that many times in COLUMN => "I"

My Code which Works when i use Button in Sheet 2 => On click

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


What I have tried:

But How do i achieve without Button Click |Macros Automatically Triggered When Value Change Occur in Sheet 2|
Posted
Updated 17-Jun-19 0:07am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

   
Comments
CHill60 17-Jun-19 6:36am
   
Beat me to it! 5'd
Maciej Los 17-Jun-19 6:59am
   
Thank you, Caroline.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100