Enter VBA multiple sheets

  • Thread starter Thread starter terilad
  • Start date Start date
T

terilad

Hi,

I have in total 150 + worksheets within a workbook, I was wondering I need
to enter a macro into each sheet, is there a quick way to do this or do I
have to enter the code by opening every worksheet,

Many thanks

Terilad
 
Hi Terilad

No, you can probably do with one single central macro. What kind of macro is
it and what does it do?

Best wishes Harald
 
It is a Private Sub Worksheet code, here it is. I can only see me having to
copy and paste into all sheets but I need to make sure that all sheets have
the code in and doing copy and paste I may miss one.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set d = Range("D:D")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
End Sub

Regards

Terilad
 
Create a Procedure module for your VBA and put your code there...

Sub MyExample()
Dim wks As Worksheet

For Each wks In Worksheets
'code that should get repeated
'for each worksheet goes here
Next wks

End Sub
 
Hi,

You can apply this macro to all sheets by putting it in 'ThisWorkbook' but
I'm unsure what the macro is supposed to be doing. Anyway I assume it does
what you want so

Alt+F11 to open VB editor. double click 'ThisWorkbook' and paste this code
in on the right

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set t = Target
Set d = Range("D:D")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
End Sub

Mike
 
I think I can only put this code in to the relevant worksheets as it related
to after input of data in certain cells this will return to sheet1

Any ideas if you think differently?

Terilad
 
There is "the relevant worksheets" now? Initial question was "I have in
total 150 + worksheets within a workbook (...) enter a macro into each
sheet" ? Details please.
 
Try Mike's suggestion.

I think you will find it works as advertised.

The event code will run only on whatever sheet is active at the time..

Beats the hell out of adding code to 150 sheets.


Gord Dibben MS Excel MVP
 
Many thanks Mike

Terilad

Mike H said:
Hi,

You can apply this macro to all sheets by putting it in 'ThisWorkbook' but
I'm unsure what the macro is supposed to be doing. Anyway I assume it does
what you want so

Alt+F11 to open VB editor. double click 'ThisWorkbook' and paste this code
in on the right

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set t = Target
Set d = Range("D:D")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
End Sub

Mike
Click to expand...
 
Back
Top