Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Actions on certain Worksheets
Hi
I have workbook which I use to monitor several data servers. This workbook has a large number of disparately named worksheets. However, I need to run a routine ONLY on worksheets with a particular naming convention. The naming convention is as follows; "Server 1 Server Details", "Server 2 Server Details" continuing incrementaly. The routine I wish to run is; Sub LinkDetail() Range("Y7").Formula = "=$A$6" Range("Y8").Formula = "=$B$6" Range("Y9").Formula = "=$Q$6" Range("Y10").Formula = "=$C$6" Range("Y11").Formula = "=$D$6" Range("Y13").Formula = "=$E$6" Range("U16").Formula = "=CONCATENATE(""Shows CPU / IO / idle time utilization (in ticks) since ASE was last started. ["",$J$6, "" microseconds per tick]"")" Range("W18").Formula = "=$G$6" Range("AA18").Formula = "=$H$6" Range("AE18").Formula = "=$I$6" Range("U23").Formula = "=$K$6" Range("Y23").Formula = "=$L$6" Range("AC23").Formula = "=$M$6" Range("U27").Formula = "=$N$6" Range("Y27").Formula = "=$O$6" Range("AC27").Formula = "=$P$6" Range("AI9").Formula = "=$R$6" Range("AJ9").Formula = "=$S$6" End Sub Can anyone out there help. Regards - Grant |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Actions on certain Worksheets
Hi Grant
something like Sub test() Dim wksSheet As Worksheet Dim i As Double i = 1 For Each wksSheet In ThisWorkbook.Worksheets If wksSheet.Name = "Server " & i & " Server Details" Then 'do your macro i = i + 1 End If Next End Sub Best regards Wolf WLamik<atgmx.net -----Original Message----- Hi I have workbook which I use to monitor several data servers. This workbook has a large number of disparately named worksheets. However, I need to run a routine ONLY on worksheets with a particular naming convention. The naming convention is as follows; "Server 1 Server Details", "Server 2 Server Details" continuing incrementaly. The routine I wish to run is; Sub LinkDetail() Range("Y7").Formula = "=$A$6" Range("Y8").Formula = "=$B$6" Range("Y9").Formula = "=$Q$6" Range("Y10").Formula = "=$C$6" Range("Y11").Formula = "=$D$6" Range("Y13").Formula = "=$E$6" Range("U16").Formula = "=CONCATENATE(""Shows CPU / IO / idle time utilization (in ticks) since ASE was last started. ["",$J$6, "" microseconds per tick]"")" Range("W18").Formula = "=$G$6" Range("AA18").Formula = "=$H$6" Range("AE18").Formula = "=$I$6" Range("U23").Formula = "=$K$6" Range("Y23").Formula = "=$L$6" Range("AC23").Formula = "=$M$6" Range("U27").Formula = "=$N$6" Range("Y27").Formula = "=$O$6" Range("AC27").Formula = "=$P$6" Range("AI9").Formula = "=$R$6" Range("AJ9").Formula = "=$S$6" End Sub Can anyone out there help. Regards - Grant . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Actions on certain Worksheets
Hi
As you have probably guessed by now, I'm pretty much a novice with VBA. I'm still battling with this particular issue, attempting run the following function against all sheets that follow a particular naming convention ie; "Server 1 Server Details", "Server 2 Server Details". The incremental numbering sequence is not particularly relevant because at some stage a number might be skipped. What is relevant however is that the first portion of the sheet name will always be "Server " and the last portion will always be " Server Details". This is where I am with my code at the moment (In my uneducated mind, this should do the trick - but seems to work on the current sheet, irrespective of the sheet name) Sub LinkDetail() Dim wksSheet As Worksheet Dim strSheet As String strSheet = "*Server Details" For Each wksSheet In ThisWorkbook.Worksheets If wksSheet.Name Like strSheet Then Range("Y7").Formula = "=$A$6" Range("Y8").Formula = "=$B$6" Range("Y9").Formula = "=$Q$6" Range("Y10").Formula = "=$C$6" Range("Y11").Formula = "=$D$6" Range("Y13").Formula = "=$E$6" Range("U16").Formula = "=CONCATENATE(""Shows CPU / IO / idle time utilization (in ticks) since ASE was last started. ["",$J$6, "" microseconds per tick]"")" Range("W18").Formula = "=$G$6" Range("AA18").Formula = "=$H$6" Range("AE18").Formula = "=$I$6" Range("U23").Formula = "=$K$6" Range("Y23").Formula = "=$L$6" Range("AC23").Formula = "=$M$6" Range("U27").Formula = "=$N$6" Range("Y27").Formula = "=$O$6" Range("AC27").Formula = "=$P$6" Range("AI9").Formula = "=$R$6" Range("AJ9").Formula = "=$S$6" End If Next End Sub Hope someone can help. Thanks to those who have responded thus far. Many Thanks - Grant |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Actions on certain Worksheets
Hi Grant,
for me your code works ok, only the sheets with "Server Details at the end are processed. Best regards Wolf -----Original Message----- Hi As you have probably guessed by now, I'm pretty much a novice with VBA. I'm still battling with this particular issue, attempting run the following function against all sheets that follow a particular naming convention ie; "Server 1 Server Details", "Server 2 Server Details". The incremental numbering sequence is not particularly relevant because at some stage a number might be skipped. What is relevant however is that the first portion of the sheet name will always be "Server " and the last portion will always be " Server Details". This is where I am with my code at the moment (In my uneducated mind, this should do the trick - but seems to work on the current sheet, irrespective of the sheet name) Sub LinkDetail() Dim wksSheet As Worksheet Dim strSheet As String strSheet = "*Server Details" For Each wksSheet In ThisWorkbook.Worksheets If wksSheet.Name Like strSheet Then Range("Y7").Formula = "=$A$6" Range("Y8").Formula = "=$B$6" Range("Y9").Formula = "=$Q$6" Range("Y10").Formula = "=$C$6" Range("Y11").Formula = "=$D$6" Range("Y13").Formula = "=$E$6" Range("U16").Formula = "=CONCATENATE(""Shows CPU / IO / idle time utilization (in ticks) since ASE was last started. ["",$J$6, "" microseconds per tick]"")" Range("W18").Formula = "=$G$6" Range("AA18").Formula = "=$H$6" Range("AE18").Formula = "=$I$6" Range("U23").Formula = "=$K$6" Range("Y23").Formula = "=$L$6" Range("AC23").Formula = "=$M$6" Range("U27").Formula = "=$N$6" Range("Y27").Formula = "=$O$6" Range("AC27").Formula = "=$P$6" Range("AI9").Formula = "=$R$6" Range("AJ9").Formula = "=$S$6" End If Next End Sub Hope someone can help. Thanks to those who have responded thus far. Many Thanks - Grant . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Actions on certain Worksheets
Hi
OK, managed to figure this one out. For anyone who has a similar type of problem, see the code below; Sub LinkDetail() Dim wksSheet As Worksheet Dim strSheet As String strSheet = "*Server Details" For Each wksSheet In ThisWorkbook.Worksheets If wksSheet.Name Like strSheet Then wksSheet.Range("Y7").Formula = "=$A$6" wksSheet.Range("Y8").Formula = "=$B$6" wksSheet.Range("Y9").Formula = "=$Q$6" wksSheet.Range("Y10").Formula = "=$C$6" wksSheet.Range("Y11").Formula = "=$D$6" wksSheet.Range("Y13").Formula = "=$E$6" wksSheet.Range("U16").Formula = "=CONCATENATE(""Shows CPU / IO / idle time utilization (in ticks) since ASE was last started. ["",$J$6, "" microseconds per tick]"")" wksSheet.Range("W18").Formula = "=$G$6" wksSheet.Range("AA18").Formula = "=$H$6" wksSheet.Range("AE18").Formula = "=$I$6" wksSheet.Range("U23").Formula = "=$K$6" wksSheet.Range("Y23").Formula = "=$L$6" wksSheet.Range("AC23").Formula = "=$M$6" wksSheet.Range("U27").Formula = "=$N$6" wksSheet.Range("Y27").Formula = "=$O$6" wksSheet.Range("AC27").Formula = "=$P$6" wksSheet.Range("AI9").Formula = "=$R$6" wksSheet.Range("AJ9").Formula = "=$S$6" End If Next End Sub Many Thanks - Grant |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Actions on certain Worksheets
Hi Grant
Try this sub Tester() set wkb = ActiveWorkBook For each wks in wkb.Worksheets If Left(wks.Name,6) = "Server" and _ Right(wks.Name,14) = "Server Details" then wks.Activate LinkDetail() end if next wks set wkb = Nothing end sub() regards Paul "Grant Reid" wrote in message ... Hi I have workbook which I use to monitor several data servers. This workbook has a large number of disparately named worksheets. However, I need to run a routine ONLY on worksheets with a particular naming convention. The naming convention is as follows; "Server 1 Server Details", "Server 2 Server Details" continuing incrementaly. The routine I wish to run is; Sub LinkDetail() Range("Y7").Formula = "=$A$6" Range("Y8").Formula = "=$B$6" Range("Y9").Formula = "=$Q$6" Range("Y10").Formula = "=$C$6" Range("Y11").Formula = "=$D$6" Range("Y13").Formula = "=$E$6" Range("U16").Formula = "=CONCATENATE(""Shows CPU / IO / idle time utilization (in ticks) since ASE was last started. ["",$J$6, "" microseconds per tick]"")" Range("W18").Formula = "=$G$6" Range("AA18").Formula = "=$H$6" Range("AE18").Formula = "=$I$6" Range("U23").Formula = "=$K$6" Range("Y23").Formula = "=$L$6" Range("AC23").Formula = "=$M$6" Range("U27").Formula = "=$N$6" Range("Y27").Formula = "=$O$6" Range("AC27").Formula = "=$P$6" Range("AI9").Formula = "=$R$6" Range("AJ9").Formula = "=$S$6" End Sub Can anyone out there help. Regards - Grant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unreadable content with no further actions | Excel Discussion (Misc queries) | |||
Hep with Conditionals actions please. | Excel Worksheet Functions | |||
Different actions on a pivotal value | Excel Worksheet Functions | |||
Repeat actions not working | Excel Discussion (Misc queries) | |||
How to perform two actions ? | Excel Worksheet Functions |