score:0

while i do not understand clearly what you want to do, i think the bellow code will help.

please adjust it to your needs.

sub add_some_fancy_rows()
dim resizer as integer
dim a as variant
a = inputbox("please enter the number of rows you want to autofill", "let me do it for you") 'first we ask for user input

on error goto notvalid  'we add an error handler, so if the user would enter text like "seven", the sub will exit with a message
resizer = cint(a)       'we store the input in a variable which has to be an integer, if the user enters text it will couse an error so we jump to the end
if resizer < 2 then goto notvalid 'we also check if the number is higher than 1, othervise it couses error, or copies the 8th row to the 9th
on error goto 0 'we reset the error handling so we can see if something else goes wrong.

thisworkbook.sheets("sheet1").visible = true
thisworkbook.sheets("sheet1").select
thisworkbook.sheets("sheet1").rows(9 + 1).entirerow.insert shift:=xldown 'add a new row under the 9th row/above the 10th row
thisworkbook.sheets("sheet1").rows(9).resize(resizer).filldown
  exit sub    'we exit the sub before the error message.
notvalid: 'in case of error we jump here
    msgbox "please enter a number which is 2 or higher"
end sub

i hope it helps


Related Query

More Query from same tag