cambiar linea de nombre de hoja por hoja activa

[dragonfire33]

como puedo cambiar la linea de codigo sheet "resultados" por hoja active o activesheet

Sub buscacuadro2()



Dim n As Integer

'--

Application.ScreenUpdating = False



'--

If [H1] = "@" Then Exit Sub

Valor = [H1]

For i = 1 To Sheets.Count

Sheets(i).Activate

For n = 1 To Len(Valor)

BuscarÁrea n, Mid(Valor, n, 1), 3, 12

BuscarÁrea n, Mid(Valor, n, 1), 16, 25



Next

Next

Sheets("resultados").Activate

Marcar Range("w28:Qw33"), Left([H1], 1)

Marcar Range("x28:x33"), Mid([H1], 2, 1)

Marcar Range("y28:y33"), Mid([H1], 3, 1)

Marcar Range("z28:z33"), Right([H1], 1)

Marcar Range("aa28:aa33"), Left([H1], 1)

Marcar Range("ab28:ab33"), Mid([H1], 2, 1)

Marcar Range("ac28:ac33"), Mid([H1], 3, 1)

Marcar Range("ad28:ad33"), Right([H1], 1)



End Sub

Sub Marcar(Rango As Range, Valor As String)

For Each Celda In Rango

x = CStr(Celda)

If x = Valor Then

Celda.Font.color = vbRed

Else

Celda.Font.color = vbBlack

End If

Next

End Sub



Sub buscaCuadro()

Dim nrop As String

'busca la combinación de nros en los cuadros de pista

Set hopi = Sheets("pista")

Sheets("resultados").Select


'limpiar pista de colores anteriores 'opcional

hopi.Range("E2:AV40").Interior.PatternColor = xlNone



'se recorre col AR de hoja resultado

For x = 2 To Range("O" & Rows.Count).End(xlUp).Row '2da lista

nrop = Range("O" & x)

For i = 2 To 50 'filas

For j = 5 To 45 Step 5 'col

If hopi.Cells(i, j) = Val(Left(nrop, 1)) And hopi.Cells(i, j + 1) = Val(Mid(nrop, 2, 1)) And hopi.Cells(i, j + 2) = Val(Mid(nrop, 3, 1)) And hopi.Cells(i, j + 3) = Val(Mid(nrop, 4, 1)) Then

filx = i: colf = j

hopi.Range(hopi.Cells(i, j), hopi.Cells(i, j + 3)).Interior.ColorIndex = 6

Exit For

End If

Next j

If hopi.Cells(i + 1, 5) = "" Then i = i + 2

Next i

Next x


End Sub

 

[offthelip]

try this:

Sub buscacuadro2()
Dim n As Integer
Cname = ActiveSheet.Name   ' Add this line
'--
Application.ScreenUpdating = False
'--
If [H1] = "@" Then Exit Sub
Valor = [H1]
For i = 1 To Sheets.Count
Sheets(i).Activate
For n = 1 To Len(Valor)
BuscarÁrea n, Mid(Valor, n, 1), 3, 12
BuscarÁrea n, Mid(Valor, n, 1), 16, 25
Next
Next
Sheets(Cname).Activate              ' change this line
Marcar Range("w28:Qw33"), Left([H1], 1)
Marcar Range("x28:x33"), Mid([H1], 2, 1)
Marcar Range("y28:y33"), Mid([H1], 3, 1)
Marcar Range("z28:z33"), Right([H1], 1)
Marcar Range("aa28:aa33"), Left([H1], 1)
Marcar Range("ab28:ab33"), Mid([H1], 2, 1)
Marcar Range("ac28:ac33"), Mid([H1], 3, 1)
Marcar Range("ad28:ad33"), Right([H1], 1)
End Sub

 

[dragonfire33]

It doesn't work