Solved VBA Code to Run on Entire Workbook

March 4, 2016 at 07:41:51
Specs: Windows 7
Below is the code from that only works on one sheet ("1")... I would like it to to work on the entire workbook.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then
If IsDate(Target) Then
Sheets("1").Cells(23, 1).Insert
Sheets("1").Cells(24, 1) = _
"Pre-Construction Meeting: " & Target.Value
Sheets("1").Cells(25, 1).Insert
End If
End If

If Target.Address = "$C$6" Then
If IsDate(Target) Then
Sheets("1").Cells(23, 1).Insert
Sheets("1").Cells(24, 1) = _
"Anticipated Start Date: " & Target.Value
Sheets("1").Cells(25, 1).Insert
End If
End If
End Sub


See More: VBA Code to Run on Entire Workbook

Report •


✔ Best Answer
March 4, 2016 at 10:44:41
Sub Worksheet_Change(ByVal Target As Range)

'Determine if Date was entered in C5
  If Target.Address = "$C$5" Then
    If IsDate(Target) Then
'Loop through sheets
     For shtNum = 1 To Sheets.Count
'Ignore this sheet
      If Sheets(shtNum).Name <> Me.Name Then
'Write to all other sheets
        With Sheets(shtNum)
          .Cells(23, 1).Insert
          .Cells(24, 1) = _
             "Pre-Construction Meeting: " & Target.Value
          .Cells(25, 1).Insert
        End With
      End If
     Next
    End If
  End If
  
'Repeat for C6
  If Target.Address = "$C$6" Then
    If IsDate(Target) Then
     For shtNum = 1 To Sheets.Count
      If Sheets(shtNum).Name <> Me.Name Then
       With Sheets(shtNum)
          .Cells(23, 1).Insert
          .Cells(24, 1) = _
             "Anticipated Start Date: " & Target.Value
          .Cells(25, 1).Insert
       End With
      End If
     Next
    End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 4, 2016 at 10:20:37
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. By enclosing the code within the pre tags, the indents will be retained and the code will be easier to read.

re: "Below is the code that only works on one sheet ("1")...I would like it to to work on the entire workbook."

I'm a little confused by that statement.

The code you posted is monitoring C5:C6 on whatever sheet whose module it is stored in and writes data to a sheet named "1".

When you say you want it to "work on the entire workbook" I don't know if you mean that you want to write the data to the entire workbook or if you want it to monitor C5:C6 on every sheet in the workbook and still write the data to the sheet named "1" or something totally different.

Please explain your goal in a little more detail.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 4, 2016 at 10:29:30
I want it to write to every sheet... not just sheet "1"

So perform that same insert line with text on each sheet. As is it is only performing on one sheet.


Report •

#3
March 4, 2016 at 10:31:25
Including the sheet on which the date was entered?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 4, 2016 at 10:34:30
No... Sorry all of the sheets except the one where the data is being pulled from.

Report •

#5
March 4, 2016 at 10:44:41
✔ Best Answer
Sub Worksheet_Change(ByVal Target As Range)

'Determine if Date was entered in C5
  If Target.Address = "$C$5" Then
    If IsDate(Target) Then
'Loop through sheets
     For shtNum = 1 To Sheets.Count
'Ignore this sheet
      If Sheets(shtNum).Name <> Me.Name Then
'Write to all other sheets
        With Sheets(shtNum)
          .Cells(23, 1).Insert
          .Cells(24, 1) = _
             "Pre-Construction Meeting: " & Target.Value
          .Cells(25, 1).Insert
        End With
      End If
     Next
    End If
  End If
  
'Repeat for C6
  If Target.Address = "$C$6" Then
    If IsDate(Target) Then
     For shtNum = 1 To Sheets.Count
      If Sheets(shtNum).Name <> Me.Name Then
       With Sheets(shtNum)
          .Cells(23, 1).Insert
          .Cells(24, 1) = _
             "Anticipated Start Date: " & Target.Value
          .Cells(25, 1).Insert
       End With
      End If
     Next
    End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 4, 2016 at 12:59:44
Worked perfectly! Thank you so much!

Report •

Ask Question