Actions on certain Worksheets

[Grant Reid]

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

 

[Wolf]

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

 

[Grant Reid]

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

 

[Guest]

Hi Grant,

for me your code works ok, only the sheets with "Server
Details at the end are processed.

Best regards

Wolf

 

[Grant Reid]

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

 

[Paul Robinson]

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