Repeat a macro

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:53 PM GMT

Question

QUESTION: I created a macro and I want to repeat itself 126 times. How will you incorporate the funtion to repeat into the existing macro? Step by step?

ANSWER: If your macro is called "Macro", then this calls that macro 126 times:

Sub MultipleTimes()

   Dim lCt as long
   For lCt = 1 to 126
       Macro
   Next

End Sub

---------- FOLLOW-UP ----------

QUESTION: Where and how I add that? into a new macro or to the existing one, where?

ANSWER: You can just paste that code beneath your existing macro. Then (from Excel) you can call the new macro by hitting alt+F8 and selecting it from the list and click run. Of course you can also add a button to a worksheet and assign the macro to that button.

---------- FOLLOW-UP ----------

QUESTION: Thanks x your prompt response but I'm still confused, let me expalin what's needed:

I created a macro that select column "V" and does text to columns and I need to repeat the text to columns process to the following 125 columns up to column "EP".

Macro needs to do text to column to all other columns. If I sue my macro is going to run 125 time on the same column "V". Then How will you record that macro to do text to columns to all those columns?


Answer

Sorry, I should have explained a bit more.

- Record your macro for the first col - Edit that macro like I did here :

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 25-8-2010 by JKP '

'

   Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
       Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
       :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

End Sub

should become:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 25-8-2010 by JKP '

'

   Selection.TextToColumns Destination:=Selection.Cells(1,1), DataType:=xlDelimited, _
       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
       Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
       :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

End Sub

Now you should be able to use this code to call the recorded macro:

Sub MultipleTimes()

  Dim lCt as long
  For lCt = 1 to 126
      Cells(1,lCt).EntireColumn.Select
      Macro1
  Next

End Sub

Advertisement

©2021 eLuminary LLC. All rights reserved.