Enter VBA multiple sheets
 

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

Enter VBA multiple sheets
 

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

"terilad" wrote in message
...
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

Enter VBA multiple sheets
 

Maybe you can store your code in a add-in
This way all workbooks can use it and it is easy if you want to make a update.

If not go to this site
http://www.cpearson.com/excel/VBE.aspx

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"terilad" wrote in message ...
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

Enter VBA multiple sheets
 

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


"Harald Staff" wrote:

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

"terilad" wrote in message
...
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

Enter VBA multiple sheets
 

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

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"terilad" wrote:

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

Enter VBA multiple sheets
 

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

"terilad" wrote:

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


"Harald Staff" wrote:

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

"terilad" wrote in message
...
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

Enter VBA multiple sheets
 

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

"Mike H" wrote:

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

"terilad" wrote:

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


"Harald Staff" wrote:

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

"terilad" wrote in message
...
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

Enter VBA multiple sheets
 

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.


"terilad" wrote in message
...
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

"Mike H" wrote:

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

"terilad" wrote:

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


"Harald Staff" wrote:

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

"terilad" wrote in message
...
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

Enter VBA multiple sheets
 

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

On Wed, 4 Mar 2009 13:55:02 -0800, terilad
wrote:

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

"Mike H" wrote:

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

"terilad" wrote:

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


"Harald Staff" wrote:

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

"terilad" wrote in message
...
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

Enter VBA multiple sheets
 

Many thanks Mike

Terilad

"Mike H" wrote:

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

"terilad" wrote:

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


"Harald Staff" wrote:

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

"terilad" wrote in message
...
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