VBA Running Continuously

alpaca678

New Member
Joined
Dec 15, 2016
Messages
4
Hi,

I want to run a VBA continuously whenever the worksheet("Name") is changing.
Basically, the logic is very simple, if anything in the worksheet("Name") is changing, copy column W and paste in column X. I attached the code below:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Name").Range("X4:X798").Value = Worksheets("Name").Range("W4:W798").Value
End Sub

I put it under Sheet1(Name) object, not in the Module 1, but every time I change a value, it returns an error message:

Run-time error '-2147418948(80010108)':
Method "Value" of object 'Range' failed



Any help would be highly appreciated. Thanks in advance :)

Best,
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Welcome to the Board!

Are you just looking to copy over changes in column W to column X?
Are the values in column W hard-coded or formulas?
 
Upvote 0
Oh I should make it clearer.

Column X (paste target column) has nothing
Column W(paste source) has formula related to column C (attached below), where that is the only column in the worksheet that I will make changes.

Column W formula = IF(Column C < 500 , "Below","Above" )
 
Upvote 0
So, let me see if I have this straight.
Column W has formulas that depend on column C, and you will be making changes to column C.
So, if you change the value in cell C44, you would need to copy the value from W44 over to X44.
Is that correct?

And I assume that you want the VALUE that the formula in column W returns placed in column X (and not just copying over the formula). Is that correct?
 
Upvote 0
Exactly!! If I put 1000 in C44, I expect my vba can put "Above" (only text, not formula)in column X44.
 
Upvote 0
So when you manually make a change to any cell in column "W" of sheet named "Alpha" you want the value entered into that cell copied into column "X" on the sheet named "Bravo" is that true.

And should this be copied into the same exact row on sheets named "Bravo" ? Or the next empty row?
 
Upvote 0
Try this code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range
    
    Set myRange = Intersect(Target, Range("C4:C798"))
    
'   Exit sub if no cells in column C updated
    If myRange Is Nothing Then Exit Sub
    
'   Update column X with values from column W
    For Each cell In myRange
        cell.Offset(0, 21).Value = cell.Offset(0, 20).Value
    Next cell

End Sub

Note: Since Event Procedure code goes right in the specific worksheet module, there is no need to include sheet references in your range references in your code (since it is all on that same sheet).
 
Last edited:
Upvote 0
You are welcome!

I hope it all makes sense.
Because all the changes are initiated by changes in column C, that is the column we want to watch.
And because each change only affects its row (and not all rows), we only need to update column X for that row (and not the entire sheet every time).
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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
Back
Top