If cell on Sheet4 is not blank/empty, then make Values of cells on Sheet1 = Values of cells on Sheet4

Question

Trying to get VBA to test if A2 on Sheet4 is not blank/empty, and if so, in other words when I paste there, to make Values of specific cells on Sheet1 to be same as Values of specific cells on Sheet4. But getting this error on second line of below code:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(ThisWorkbook.Sheets("sheet4").Range("a2").Value) = False Then
ThisWorkbook.Sheets("sheet1").Range("k6").Value = ThisWorkbook.Sheets("sheet4").Range("a2").Value
ThisWorkbook.Sheets("sheet1").Range("L6").Value = ThisWorkbook.Sheets("sheet4").Range("B2").Value
ThisWorkbook.Sheets("sheet1").Range("M6").Value = ThisWorkbook.Sheets("sheet4").Range("C2").Value
ThisWorkbook.Sheets("sheet1").Range("N6").Value = ThisWorkbook.Sheets("sheet4").Range("D2").Value
ThisWorkbook.Sheets("sheet1").Range("O6").Value = ThisWorkbook.Sheets("sheet4").Range("E2").Value
ThisWorkbook.Sheets("sheet1").Range("P6").Value = ThisWorkbook.Sheets("sheet4").Range("F2").Value
ThisWorkbook.Sheets("sheet1").Range("Q6").Value = ThisWorkbook.Sheets("sheet4").Range("G2").Value
ThisWorkbook.Sheets("sheet1").Range("R6").Value = ThisWorkbook.Sheets("sheet4").Range("H2").Value
ThisWorkbook.Sheets("sheet1").Range("S6").Value = ThisWorkbook.Sheets("sheet4").Range("I2").Value
'FIRST 9
ThisWorkbook.Sheets("sheet1").Range("L7").Value = ThisWorkbook.Sheets("sheet4").Range("J2").Value
ThisWorkbook.Sheets("sheet1").Range("M7").Value = ThisWorkbook.Sheets("sheet4").Range("K2").Value
ThisWorkbook.Sheets("sheet1").Range("N7").Value = ThisWorkbook.Sheets("sheet4").Range("L2").Value
ThisWorkbook.Sheets("sheet1").Range("O7").Value = ThisWorkbook.Sheets("sheet4").Range("M2").Value
ThisWorkbook.Sheets("sheet1").Range("P7").Value = ThisWorkbook.Sheets("sheet4").Range("N2").Value
ThisWorkbook.Sheets("sheet1").Range("Q7").Value = ThisWorkbook.Sheets("sheet4").Range("O2").Value
ThisWorkbook.Sheets("sheet1").Range("R7").Value = ThisWorkbook.Sheets("sheet4").Range("P2").Value
ThisWorkbook.Sheets("sheet1").Range("S7").Value = ThisWorkbook.Sheets("sheet4").Range("Q2").Value
'last 8
End If
End Sub

Here's Project Pane:

Another error pic, while trying to fix second line for Sheet code name:

You sure it is called `sheet4`? that usually means that sheet does not exist in the workbook. — Scott Craner, Jul 18 '22 at 19:13
I am fully aware that you can many many sheets. That does not change what that error is saying. It is saying cannot find a sheet named `sheet4`. — Scott Craner, Jul 18 '22 at 19:19
Do this, [edit] your post to include a screen grab of the project pane with only the workbook where the code is found expanded. — Scott Craner, Jul 18 '22 at 19:27
There we go. `ThisWorkbook.Sheets("sheet4")` is not the code name. it is the value inside the `()` and that is why it is not finding it. Change the `"sheet4"` to what is in the `()` also do that with the `"sheet1"` — Scott Craner, Jul 18 '22 at 19:39
Or use the code name like this: `Sheet4.Range("a2").Value` same with `sheet1`. You refer to the code name differently than the sheet name. — Scott Craner, Jul 18 '22 at 19:40
This is a duplicate of: https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename — Scott Craner, Jul 18 '22 at 19:42
you have a `ThisWorkbook.` that should not be there on the error line. — Scott Craner, Jul 18 '22 at 20:00
Added another error pic to OP, while trying to fix second line for Sheet code name. What's the right way? @ScottCraner & thanks, getting back into VBA, a bit rusty. — 2Took, Jul 18 '22 at 20:00

VBasic2008 · Accepted Answer · 2022-07-18T20:15:32.277

0

A Worksheet Change: Write Values to Another Worksheet

Note that your posted image is showing the code names, not the (tab) names of the worksheets, the ones in parentheses.
Also, the image is clearly showing that the worksheet, whose code name is Sheet1 is not named Sheet1 but has a longer name hence Run-time erorr '9': Subscript out of range.
In your code image, ThisWorkbook.Sheet4 is obviously invalid. You can either use Sheet4 or the worse ThisWorkbook.Worksheets("Sheet4") or the worst choice ThisWorkbook.Worksheets(4).
Note that Me refers to the sheet in which module the code is in. You could omit Me. in the code or in this particular case, you could replace it with Sheet4..

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Define constants.        
    Const tCellAddress As String = "A2"
    Dim Cells1() As Variant: Cells1 = Array("K6:S6", "L7:S7")
    Dim Cells4() As Variant: Cells4 = Array("A2:I2", "J2:Q2")
    
    ' Reference the target cell ('tCell').
    Dim tCell As Range: Set tCell = Me.Range(tCellAddress)
    
    ' Check if the target cell was not changed.
    If Intersect(tCell, Target) Is Nothing Then Exit Sub
        
    ' Check if the target cell is blank.
    If Len(CStr(tCell.Value)) = 0 Then Exit Sub
    
    ' Write the values.
    Dim n As Long
    For n = LBound(Cells1) To UBound(Cells1)
        Sheet1.Range(Cells1(n)).Value = Me.Range(Cells4(n)).Value
    Next n
    
End Sub

edited Jul 18 '22 at 20:15

answered Jul 18 '22 at 19:54

VBasic2008

44,888
5
17
28

your code looks slick, but aside from that --what benefits does it offer over solution I posted? – 2Took Jul 18 '22 at 20:15
It is more efficient. It reads and writes twice, while your code does it 17 times. It doesn't get triggered if cell `A2` hasn't been manually changed, while your code gets triggered if any of the cells have been manually changed. – VBasic2008 Jul 18 '22 at 20:19
Could you elaborate on your second sentence? It seems code I posted gets triggered as intended, which is if A2 on Sheet4 is no longer blank. @VBasic2008 – 2Took Jul 18 '22 at 20:27
Your code gets triggered every time you manually enter a value into any cell in `Sheet4` or copy/paste values or use VBA to write values. You can easily prove this by e.g. using `MsgBox Target.Address` right above `End Sub` in your code, while if you put this to the same place in my procedure, you'll notice the difference. – VBasic2008 Jul 18 '22 at 20:32
To clarify though, by triggered you only mean that VBA is checking to see if A2 is still empty, and not that unintended results will occur, correct? The code beyond second line will only run if A2 is no longer empty, which is what the intention is. @VBasic2008 Your code looks superior, no doubt, and I will try to learn form it. – 2Took Jul 18 '22 at 20:42
Your code does what you describe, but once the cell is no longer empty, it will unnecessarily (fully) run every time any of the other cells in `Sheet4` is changed, possibly slowing down your worksheet. – VBasic2008 Jul 18 '22 at 20:46

score 0 · Answer 2 · answered Jul 18 '22 at 20:11

Here's a working code:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Sheet4.Range("a2").Value) = False Then
Sheet1.Range("k6").Value = Sheet4.Range("a2").Value
Sheet1.Range("L6").Value = Sheet4.Range("B2").Value
Sheet1.Range("M6").Value = Sheet4.Range("C2").Value
Sheet1.Range("N6").Value = Sheet4.Range("D2").Value
Sheet1.Range("O6").Value = Sheet4.Range("E2").Value
Sheet1.Range("P6").Value = Sheet4.Range("F2").Value
Sheet1.Range("Q6").Value = Sheet4.Range("G2").Value
Sheet1.Range("R6").Value = Sheet4.Range("H2").Value
Sheet1.Range("S6").Value = Sheet4.Range("I2").Value
'FIRST 9
Sheet1.Range("L7").Value = Sheet4.Range("J2").Value
Sheet1.Range("M7").Value = Sheet4.Range("K2").Value
Sheet1.Range("N7").Value = Sheet4.Range("L2").Value
Sheet1.Range("O7").Value = Sheet4.Range("M2").Value
Sheet1.Range("P7").Value = Sheet4.Range("N2").Value
Sheet1.Range("Q7").Value = Sheet4.Range("O2").Value
Sheet1.Range("R7").Value = Sheet4.Range("P2").Value
Sheet1.Range("S7").Value = Sheet4.Range("Q2").Value
'last 8
End If
End Sub

If cell on Sheet4 is not blank/empty, then make Values of cells on Sheet1 = Values of cells on Sheet4

2 Answers2

A Worksheet Change: Write Values to Another Worksheet