| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| enter data in the same column in multiple sheets | New Users to Excel | |||
| Enter row into multiple sheets? | Excel Discussion (Misc queries) | |||
| Multiple text sheets into a master worksheet as you enter data | Excel Worksheet Functions | |||
| how do i enter data in multiple sheets simultaneously | Excel Worksheet Functions | |||
| Enter Text or Date on Multiple Sheets | Excel Programming | |||
Hybrid Mode
