Code to COPY VBA from Sheet1 and add Sheets 2 - 10

[Randy11]

Am looking for a simple way to copy the VBA code for Sheet1 to Sheets2 - 10. Note there are a few other worksheets in the workbook that do not require this code.

for sample sake, with VBA code below on found on Sheet1 module add same code to Sheets2 to 10

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Target
If Target.Row = 25 And Target.Column = 5 Then
Range("E30").Select
End If
If Target.Row = 26 And Target.Column = 5 Then
Range("E30").Select
End If
End With
End Sub

 

[SkipVought]

hi,

Why not use the Workbook_SheetSelectionChange event, rather than code for individual sheet events?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  With Target
    If Target.Row = 25 And Target.Column = 5 Then
      sh.Range("E30").Select
    End If
    If Target.Row = 26 And Target.Column = 5 Then
      sh.Range("E30").Select
    End If
  End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Hey Skip Not all sheets use this same code, only sheets 1 - 10...

 

[SkipVought]

select case CInt(Mid(Sh.CodeName,6,Len(Sh.CodeName)-5))
   case 1 to 10
      'put your code here
end select

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

oops!!!

I realized the moment I submitted that I included a conversion error.

select case Mid(Sh.CodeName,6,Len(Sh.CodeName)-5)
   case "1" to "9", "10"
      'put your code here
end select

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

While I attempt to apply what you have indicated, is there a way to do what I had asked to do?

 

[SkipVought]

All you have to do is all within the workbook event procedure previously posted .

The code just posted goes within the event and the code that was within thee event goes within the select case

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Skip, just provided sample code. Per usual there is other code involved in the three areas noted below.

Ideally to start is there a way to do what was requested? This would save much needed time initially.

In the longer term can definitely see the benefit of taking the approach you have pointed out.
For this type of code
Private Sub Worksheet_SheetChange
Private Sub Worksheet_SheetSelectionChange
Private Sub Worksheet_SheetCalculate

Is this close?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range
select case Mid(Sh.CodeName,6,Len(Sh.CodeName)-5)
case "1" to "9", "10"
'code here
end select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range
select case Mid(Sh.CodeName,6,Len(Sh.CodeName)-5)
case "1" to "9", "10"
'code here
end select
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object, ByVal Target As Range
select case Mid(Sh.CodeName,6,Len(Sh.CodeName)-5)
case "1" to "9", "10"
'code here
end select
End Sub

 

[SkipVought]

The Calculate event has no Target!, Simply check out the code in the ThisWorkbook Object or any Worksheet object.

Example

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  select case Mid(Sh.CodeName,6,Len(Sh.CodeName)-5)
     case "1" to "9", "10"
        'put your code here
        With Target
          If .Row = 25 And .Column = 5 Then
            sh.Range("E30").Select
          End If
          If .Row = 26 And .Column = 5 Then
            sh.Range("E30").Select
          End If
  End With

  end select 
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

1) Code is being applied to all worksheets? Only want to work on Sheets 1 to 10. Also, will the sheet tab name make a difference?

2) Also have applied to Worksheet calculate code as follows:
Am getting Compile Error, Can't assign to read only property. Me.Name is highlighted in code. Works fine in the worksheet?

Private Sub Workbook_Sheet_Calculate(ByVal Sh As Object, ByVal Target As Range)
Select Case Mid(Sh.CodeName, 6, Len(Sh.CodeName) - 5)
Case "1" To "9", "10"

'put your code here
With Target
If Range("B2").Text <> Me.Name Then Me.Name = Range("B2").Text
End With

End Select
End Sub

 

[SkipVought]

1) Code is being applied to all worksheets? Only want to work on Sheets 1 to 10. Also, will the sheet tab name make a difference?

The Select Case LIMITS to the sheets in question, assuming that the CodeNames are Sheet1, Sheet2, etc. If not, then just list the code names as...

   Case "SheetX","YourSpecialName"

et cetera. If you use CodeName that YOU can control, then the user can change the Sheet.Name and it makes no difference.

As I stated previously, the Calculate events have no TARGET argument!!!

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

What object is Me?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Sheet names are changed in the workbook so the Special name method does not work.

Is there a way to do what was 1st asked?

Can VBA Code in Sheet1 Module be copied & added to sheets 2 - 10 with VBA. Or could be copied from a specific sheet & added to 1st 9 sheets in the workbook.

 

[SkipVought]

Sheet names are changed in the workbook so the Special name method does not work.

That is the VERY REASON that CodeNames are used, because ONLY YOU CAN CHANGE THE CodeName!!!

What is your problem with my posted solution?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

So is the issue that 1) you have a FIXED number of known sheets and an unknown number of ADDED sheets and the ADDED sheets need to have this code applied?

If thats the case, then the logic could be like this

Select Case sh.CodeName
   Case "FixedSheet1","FixedSheet2"    '[b]list all CodeSheet Names that you do NOT want the code to run on[/b]
   Case Else
      'put your code here that you want to run on ADDED sheets, [b]referencing them properly[/b]
End Select

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]