VBA Script

camper1337

New Member
Joined
Jan 7, 2018
Messages
6
I have a script that I have written. The script works well, but only on Sheet 1. If I enter data into sheets 2-5, the end result of the vba still goes to Sheet 1. I'd like to make the script work individually on each sheet. Thanks in advance. Code Below.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   ''Do something only if the value changes in cell B9
   If Target.Address = Range("B9").Address Then
   Range("C8").Value = Range("C9")
   If Target.Address = "$B$9" Then
      ''Look at the full list below the Target title
      With ThisWorkbook.Names("Reading_Date").RefersToRange.CurrentRegion
         ''Look at the cell at the bottom of the list
         With .Offset(.Rows.Count, 0).Resize(1, 1)
             ''Enter the current time in the cell
             .Value = Format(Date, "Short Date")
             ''Enter the new value to the right of the time
             .Offset(0, 1).Value = Target.Value
             .Offset(0, 2).Value = Range("B11")
             .Offset(0, 3).Value = Range("B20")
          End With
      
      End With
   End If
   End If
End Sub
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
You could try using the workbook level event SheetChange.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''Do something only if the value changes in cell B9

    If Target.Address = "$B$9" Then
        Sh.Range("C8").Value = Sh.Range("C9").Value
        ''Look at the full list below the Target title
        With ThisWorkbook.Names("Reading_Date").RefersToRange.CurrentRegion
            ''Look at the cell at the bottom of the list
            With .Offset(.Rows.Count, 0).Resize(1, 1)
                ''Enter the current time in the cell
                .Value = Format(Date, "Short Date")
                ''Enter the new value to the right of the time
                .Offset(0, 1).Value = Target.Value
                .Offset(0, 2).Value = Sh.Range("B11").Value
                .Offset(0, 3).Value = Sh.Range("B20").Value
            End With

        End With
    End If

End Sub
 

camper1337

New Member
Joined
Jan 7, 2018
Messages
6
Now it is not posting the end result anywhere. I'm not super familiar with VB to begin with so pardon my struggle to understand.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Where did you put the code I suggested?

It should go in the ThisWorkbook module.
 

camper1337

New Member
Joined
Jan 7, 2018
Messages
6

ADVERTISEMENT

I did have it in the wrong place. I moved it to the correct module and I have the same issue. If I put data in cell B9 on sheet 2, it still outputs on sheet 1
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Which sheet is the named range Reading_Date on?
 

camper1337

New Member
Joined
Jan 7, 2018
Messages
6

ADVERTISEMENT

All of them. That's why I'm trying to get the code to run on each sheet individually.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Perhaps this then.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''Do something only if the value changes in cell B9

    If Target.Address = "$B$9" Then
        Sh.Range("C8").Value = Sh.Range("C9").Value
        ''Look at the full list below the Target title
        With Sh.Range("Reading_Date").CurrentRegion
            ''Look at the cell at the bottom of the list
            With .Offset(.Rows.Count, 0).Resize(1, 1)
                ''Enter the current time in the cell
                .Value = Format(Date, "Short Date")
                ''Enter the new value to the right of the time
                .Offset(0, 1).Value = Target.Value
                .Offset(0, 2).Value = Sh.Range("B11").Value
                .Offset(0, 3).Value = Sh.Range("B20").Value
            End With

        End With
    End If

End Sub
 

camper1337

New Member
Joined
Jan 7, 2018
Messages
6
Gives a runtime error of '1004' Application Defined or Object defined error. Debugging shows error on line 7
Code:
With Sh.Range("Reading_Date").CurrentRegion
 

camper1337

New Member
Joined
Jan 7, 2018
Messages
6
I forgot to mention that it does work on Sheet 1. Just not the other four sheets that I have.
Gives a runtime error of '1004' Application Defined or Object defined error. Debugging shows error on line 7
Code:
With Sh.Range("Reading_Date").CurrentRegion
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

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
Top