run code from another worksheet but same workbook

[Trevor3007]

good evening,

I use this code:-

Sub Gencode()


ActiveSheet.Unprotect





Static IsRandomized As Boolean
Dim i As Integer, PW1 As String
Dim cell As Range, PW As String

If Not IsRandomized Then Randomize: IsRandomized = True

For Each cell In Range("b3:b199")
PW = vbNullString
For i = 1 To 8
Do
DoEvents
PW1 = Chr(Int((97 - 122 + 1) * Rnd + 122)) ' Lower case alpha
Loop Until InStr(1, PW, PW1, 1) = 0
PW = PW & PW1
Next i
PW = Replace(PW, Mid(PW, Int(8 * Rnd + 1), 1), Int(8 * Rnd + 1))
cell.Value = PW
Next cell

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True



End Sub


it runs fine if I just run it from the applicable worksheet. My issue is I want to run this from another worksheet within the workbook . If I try to run from another worksheet it puts the code into the wrong worksheet column.

Is anyone able to solve this for me

KR
Trevor3007

 

[mrhstn]

Hi

If you want it to perform its actions in a specific worksheet then you will need to be explicit.

e.g. Sheets("SheetNameHere").Range("b3:b199")

otherwise any generic code will run just wherever you run it.

 

[Trevor3007]

hi & thank you for your swift reply.

The worksheet/tab name is called 'Code' could you kindly place the applicable into the code I use for me , as I have tried to do it myself but just get errors when I try ?

thanks again
Trevor3007

 

[mrhstn]

You need to keep trying or you will never learn... mistakes and errors are good (so long as you learn from them )

Sub Gencode()
Static IsRandomized As Boolean
Dim i As Integer, PW1 As String
Dim cell As Range, PW As String


With Sheets("Code") ' Change Code to the name of the sheet this is to work on.
    .Unprotect




    If Not IsRandomized Then Randomize: IsRandomized = True


    For Each cell In .Range("b3:b199")
        PW = vbNullString
            For i = 1 To 8
                Do
                DoEvents
                    PW1 = Chr(Int((97 - 122 + 1) * Rnd + 122)) ' Lower case alpha
                Loop Until InStr(1, PW, PW1, 1) = 0
            PW = PW & PW1
            Next i
        PW = Replace(PW, Mid(PW, Int(8 * Rnd + 1), 1), Int(8 * Rnd + 1))
        cell.Value = PW
    Next cell


    .Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingCells:=True
End With
End Sub

 

[Trevor3007]

thanks ...works a treat....

I always try to solve my prob before I ask 'mr excel' , either way I learn.

KR
Trevor3007