vba calling a macro based on cell value

[tommyleinen]

Hello, I wonder if anyone can advise on if the following is possible? I have a sheet with various weeks' data, and there is a small macro to sort by week for each week in the sheet. So there are macros: sort1, sort2, sort3, sort4 etc.

What I would like to do is call the relative macro within another macro based on a cell value. EG A1 =1, then call sort1; If A1 = 2, then the part in the VBA would call sort2. etc. I don't want the macro called when the cell is changed, just to be able to write the code which I think might be along the lines of:

call sort & Sheets("Sheet1").Range("A1").Value

Is something like this possible?

Thanks in advance !

 

[Osvaldo Palmeiro]

Hello.
Try:

Sub callsort()
 Dim myMacro As String
  myMacro = "sort" & [A1]
  Run myMacro
End Sub

 

[tommyleinen]

Hello.
Try:

Sub callsort()
 Dim myMacro As String
  myMacro = "sort" & [A1]
  Run myMacro
End Sub

Hi Osvaldo, thanks for your reply. I will try this when I get a chance later. How would I integrate that to an existing macro?

Do I make the declarations:

Dim myMacro As String
myMacro = "sort" & [A1]

At the top of the existing macro and then use "Run myMacro" whenever needed?

Thanks

 

[My Aswer Is This]

So you have 52 separate macros for each week of the year? I would like to see your macro for sorting.

 

[tommyleinen]

So you have 52 separate macros for each week of the year? I would like to see your macro for sorting.

Haha not quite, but yes they are seperate macros, I probably could have simplified them but I didn't anticipate the sheet growing arms legs and siblings. There are 17 sort macros.