Can I use things like last row found in one macro in another or do I have to do it again?

[tonywatsonhelp]

Hi Everyone,

I often have macros that use the same commands with in them

Like copy and paste to "Lastrow" having use something like

Lastrow = Sheets("Hours").Cells(Rows.Count, "A").End(xlUp).Row

Now the problem I get is I find I'm repeating a lot of my core code over and over
I was woundering is it possible to stop excel forgeting what I've set lastrow as if I have not ended the sub its in, but simply run another macro within it?

For Example

Sub Main_Macro()
Lastrow = Sheets("Hours").Cells(Rows.Count, "A").End(xlUp).Row
Call Test1
Range("AA2").PasteSpecial xlPasteValues
Call Test2
Range("BA2").PasteSpecial xlPasteValues
End Sub


Sub Test1()
Range("A2", "B" & Lastrow).Copy
End Sub

Sub Test2()
Range("I2", "K" & Lastrow).Copy
End Sub

So my question is can I carry over the last row value t=into a new macro? and how

 

[hiker95]

tonywatsonhelp,

You could try and use a Public variable.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Public Lastrow As Long

Sub Main_Macro()
Lastrow = Sheets("Hours").Cells(Rows.Count, "A").End(xlUp).Row
Call Test1
Range("AA2").PasteSpecial xlPasteValues
Call Test2
Range("BA2").PasteSpecial xlPasteValues
End Sub

Sub Test1()
Range("A2", "B" & Lastrow).Copy
End Sub

Sub Test2()
Range("I2", "K" & Lastrow).Copy
End Sub

 

[tonywatsonhelp]

That's a great Idea Hiker 95,
but would that mean that I can only use "Lastrow =" once in a workbook? or does it mean its public to all excel docs?

 

[Joe4]

Why not just pass the variable to your other procedures?
You can set up your procedures to pass and accept variables, i.e.

Sub Main_Macro()
Lastrow = Sheets("Hours").Cells(Rows.Count, "A").End(xlUp).Row
Call Test1[COLOR=#ff0000](Lastrow)[/COLOR]
Range("AA2").PasteSpecial xlPasteValues
Call Test2[COLOR=#ff0000](LastRow)[/COLOR]
Range("BA2").PasteSpecial xlPasteValues
End Sub


Sub Test1([COLOR=#ff0000]myLastRow as Long[/COLOR])
Range("A2", "B" & [COLOR=#ff0000]myLastrow[/COLOR]).Copy
End Sub

Sub Test2([COLOR=#FF0000]myLastRow as Long[/COLOR])
Range("I2", "K" & [COLOR=#ff0000]myLastrow[/COLOR]).Copy
End Sub

 

[tonywatsonhelp]

Thanks Joe,

that's more what I'm looking for so thank you,

If I carry more than one variable Do I just use Commas like , "Call Test2(LastRow, FirstRow)" for example?

 

[Joe4]

If I carry more than one variable Do I just use Commas like , "Call Test2(LastRow, FirstRow)" for example?

Exactly.

You can even make some of the arguments optional.
See: Passing Arguments to Procedures

 

[hiker95]

That's a great Idea Hiker 95,
but would that mean that I can only use "Lastrow =" once in a workbook? or does it mean its public to all excel docs?

tonywatsonhelp,

My understanding of a Public variable, is that it is available to all the other macros in the Excel Workbook.

Have you tried the macro code in my reply #2?

 

[tonywatsonhelp]

Hi Hiker,
The "Public variable" will be very usefull for me, especially for the documents with less macros in them, the only problem I can forsee is I tend to use the same wording in different macros so I might have used "Lastrow" before then if I make it public it wont know what one to use, however as time goes on if this proves as helpful as I think it will then I'll change the way I do thinks anyway so this can be used more often.

Thanks for your help
Tony

 

[hiker95]

Hi Hiker,
The "Public variable" will be very usefull for me, especially for the documents with less macros in them, the only problem I can forsee is I tend to use the same wording in different macros so I might have used "Lastrow" before then if I make it public it wont know what one to use, however as time goes on if this proves as helpful as I think it will then I'll change the way I do thinks anyway so this can be used more often.

Thanks for your help
Tony

tonywatsonhelp,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.