Use variable within excel function

[spencer_time]

Hello, I have some test code that I can't get to work correctly. I am trying to figure out if it's possible to use a variable within an excel function, and if so, the proper way to do that.

The following is what I currently have, it is not working:

Sub embedVarFunc()

    Dim rng As Range
    Dim num As Integer
    num = 1
    Set rng = Sheet&num.Range("A1").CurrentRegion 'attempt to use variable as part of function
    
    MsgBox rng.Address
    
End Sub

If any of you know how to do this properly, any advice would be appreciated.
(the end use will be within a large spreadsheet that imports files to sheets, names those sheets after the filename of the originating file, and performs operations on the sheets with regard to the file name{which is in the format 01CEQ09, and the first two numbers will be extracted into a variable and used for further sorting and operations})

Thanks in advance for any advice.

 

[Fluff]

For sheet names try

Set Rng = Sheets("Sheet" & num).Range("A1").CurrentRegion

 

[DanteAmor]

To use the name of a sheet as a variable, it can be like this:

For example, if the sheet is called "1", then:

Sub embedVarFunc()


    Dim rng As Range
    Dim num As [COLOR=#ff0000]String[/COLOR]
[COLOR=#ff0000]    num = "1"[/COLOR]
    Set rng = [COLOR=#ff0000]Sheets(num)[/COLOR].Range("A1").CurrentRegion 'attempt to use variable as part of function
    
    MsgBox rng.Address
    
End Sub

 

[spencer_time]

For sheet names try

Set Rng = Sheets("Sheet" & num).Range("A1").CurrentRegion

Thank you Fluff, this is exactly what I needed! You have been very helpful to me with regards to my spreadsheet I'm working on.

 

[Fluff]

Glad we could help & thanks for the feedback