Passing a variable into a different piece of code

[bruty]

I have a spreadsheet that has several buttons on that run code using the cells around the code for parts of the data. eg

ColumnA1 ColumnB1 ColumnC1 Button1
ColumnA2 ColumnB2 ColumnC2 Button2

I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)

I am having problems getting the variable to pass - can anyone offer any help?

 

[texasalynn]

set the variable up as public
edit:
I'm not 100% sure that the variable is still available if the code has finished, if that's what I think you are stating.

 

[bruty]

I only need it while the code is running.

Where do I set this? In the first bit of code?

 

[RoryA]

Set up your second piece of code to take the information as an argument - for example:

Sub SecondSub(lngInput as Long)
   ' code to do something with lngInput here
End Sub

then you call it from your buttons using:

Private Sub Button1_Click()
  ' code that does some unique stuff
  ' call second sub and pass the value 1 to it
  SecondSub 1
End Sub

Does that make sense?

 

[bruty]

Here's the code I have to start with:

Sub Emailer_ChrisBruty()


emailto = "Chris Bruty"

Range("C3").Select
Selection.Copy
Sheets("Fetch").Select
Range("b2").Select
ActiveSheet.Paste
Sheets("Standard Reports").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Fetch").Select
Range("b3").Select
ActiveSheet.Paste
Sheets("Standard Reports").Select
Range("E3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Fetch").Select
Range("b4").Select
ActiveSheet.Paste
Sheets("Standard Reports").Select
Range("F3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Fetch").Select
Range("b5").Select
ActiveSheet.Paste
Sheets("Standard Reports").Select
Range("G3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Fetch").Select
Range("b12").Select
ActiveSheet.Paste


emailCode

Windows("Weekly Breakdown.xls").Activate
Sheets("Standard Reports").Select
Range("J3").Value = Now

EndSub:
End Sub

The variable I want to pass over is the emailto and I want to pass it into the emailCode macro that is called at the bottom.

 

[texasalynn]

I only need it while the code is running.

Where do I set this? In the first bit of code?

before the sub put
Public VAR as Long
(replace with what your variable is called)
This will store the value from the 1st macro and pass to the 2nd macro, by using the call like Rorya showed

 

[ScottR]

Then make sure emailCode is defined as follows

Sub emailCode(mailto as string)

'Code in here

end sub

That makes the mailto variable available in your emailcode sub. To pass the data to it amend the call in your Emailer_ChrisButy() sub to

call emailacode(emailto)

instead of just

emailacode

 

[ScottR]

before the sub put
Public VAR as Long
(replace with what your variable is called)
This will store the value from the 1st macro and pass to the 2nd macro, by using the call like Rorya showed

You don't need a Public Variable if passing from one sub to another.

 

[bruty]

Cheers. Will give it a go.

 

[texasalynn]

as just a FYI

Project-Level, Workbook Level, or Public Module-Level

These variables are dimensioned at the top of any standard public module, like shown below;

Public lRows as Long
All variables dimensioned at this level are available to all Procedures in all Modules. Its value is retained unless the Workbook closes or the End Statement is used.