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 =>

Sheet 2 as =>

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 :

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|
Updated 17-Jun-19 0:07am

1 solution

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

Solution 1

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