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

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
567
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
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
The macro you posted will work on whatever sheet is active.
 

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
567
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jan 26, 2017
Messages
567

ADVERTISEMENT

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

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
I can't follow all this.
No doubt someone else will help.
 

Forum statistics

Threads
1,136,637
Messages
5,676,924
Members
419,659
Latest member
ShalDRH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top