run a macro on sheet 1 but the macro is on sheet 3

[Trevor3007]

good morning,

I want to run a macro that is on sheet 3 (sheet name is CODE ) from sheet 1 (sheet name is LT ) The code is:-

Sub autocode()
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("b2:b200")
PW = vbNullString
For i = 1 To 8
Do
DoEvents
PW1 = Chr(Int((96 - 123 + 1) * Rnd + 123)) ' 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

End Sub


Can anyone help?

Thank you.
KR
Trevor3007

 

[footoo]

The macro you posted will work on whatever sheet is active.

 

[Trevor3007]

thank you for the quick reply. Yes it does, but it places the data into a column that is used for other purposes which is on sheet 1 (tab name LT). The VB should only change the data that is on sheet 3 (tab name CODE).

Sheet one (cell B3), will only insert the info from sheet 3 when it meets the criteria set in cell A3 (sheet 1)

Hope this makes sense & thank you

 

[footoo]

thank you for the quick reply. Yes it does, but it places the data into a column that is used for other purposes which is on sheet 1 (tab name LT). The VB should only change the data that is on sheet 3 (tab name CODE).

Sheet one (cell B3), will only insert the info from sheet 3 when it meets the criteria set in cell A3 (sheet 1)

Hope this makes sense & thank you

The macro places random strings in B2:B200 on whatever sheet is active.
If you want it to work on sheet "CODE" only, put at the start of the macro : Sheets("CODE").Activate

Don't understand what you want re this :
"Sheet one (cell B3), will only insert the info from sheet 3 when it meets the criteria set in cell A3 (sheet 1)"

 

[Trevor3007]

many thanks again.

Am sorry to ask, but could you place into the code I sent '

Sheets("CODE").Activate' As I am unsure where to place it.

As for you don't understand:-
"Sheet one (cell B3), will only insert the info from sheet 3 when it meets the criteria set in cell A3 (sheet 1)"

I will try to explain.

On sheet 1, I need to generate a random code which goes into B3. However B3 has a a vlookup and will place the random code once cell a3 contains data.

The VB is on sheet 3. A new set of random code has to be done every day. So rather than go to sheet 3 & press the macro button and that will change the code , I just want to run the same VB on sheet 1, which will then put the code into the applicable cell (in this case B3) after it has changed the applicable on sheet 3 cell b2:b2007

Thank you again & hope this makes sense.


_{^{<strike>
</strike>}}​

 

[footoo]

I can't follow all this.
No doubt someone else will help.

 

[Trevor3007]

many thanks for your time anyhoos.

fingers crossed & soon