Running macro x # of times with a prompt

[Guest]

I have this macro and i want to have a prompt come up and ask how many times
the user wants to run it.. Thanks in advance

Sub New_Contractor()
'
' New_Contractor Macro
' Macro recorded 8/3/2006 by Parsons User
'

'
ActiveSheet.Protect UserInterfaceOnly:=True
Rows("20:50").Select
Selection.Copy
Rows("51:51").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("D52:I52").Select
ActiveWindow.SmallScroll Down:=8
End Sub

 

[Otto Moehrbach]

Something like this perhaps. HTH Otto
Sub New_Contractor()
Dim Num As Long
Dim c As Long
Num = Application.InputBox(Prompt:="Enter the number desired", Type:=1)
Application.ScreenUpdating = False
For c = 1 To Num
ActiveSheet.Protect UserInterfaceOnly:=True
Rows("20:50").Select
Selection.Copy
Rows("51:51").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("D52:I52").Select
ActiveWindow.SmallScroll Down:=8
Next c
Application.ScreenUpdating = True
End Sub

 

[Guest]

Declare variables:
Dim vTimes as Variant, vCount as Variant

Sub NewContractor()
vTimes = Inputbox("How many times do you want to run this macro")
For vCount = 1 to vTimes step 1
ActiveSheet.Protect UserInterfaceOnly:=True
Rows("20:50").Copy
Rows("51:51").Insert Shift:=xlDown
Range("D52:I52"). and whatever you want to do with it
Next
End Sub

You do not have to select to act! It slows down your macros, and makes your
code a nightmare.

 

[Guest]

Kassie, i followed your instructions and this is what i get..im a newbie so
not sure if i should of replaced some text with something.
I get a ms visual basic compile error expected:sub or function

declare variables is red and so is range ("D52:I52")

 

[Guest]

Declare variables is what I wanted you to do. Remove the words Declare
variables from your module!
I don't know what you want to do with D52:I52? What happens if you just say
Range("D52:I52").Select

 

[Guest]

Kassie,
Thanks that works great now. the D52:I52 was where i had placed my cursor
after i had cut and pasted the data i needed, is where i placed my cursor and
gave the macro the lost focus signal, im not sure if that makes sense im
thinking i dont even need it.

 

[Guest]

You're right, you do not need that!

Kassie,
Thanks that works great now. the D52:I52 was where i had placed my cursor
after i had cut and pasted the data i needed, is where i placed my cursor and
gave the macro the lost focus signal, im not sure if that makes sense im
thinking i dont even need it.