code not working

[steve hill]

Hi I got the code below someone on this site about a year ago but seems to have stopped working, when I run the macro I get the selection box, I then select the require range in column A this is entered into box, when I click ok nothing else happens. I can only assume its not excepting my selection. the only thing diffrent from last year is I have open last years file and saved as changing 2007 to 2008 in file name, any help would be appreciated

Sub finished_make_selection_to_add_blank_rows()
'Erik Van Geit
'060630

Dim tmp As Range
Dim rng As Range
Dim FR As Long          'First Row
Dim LR As Long          'Last Row
Dim CR As Long          'Count Rows
Dim NR As Integer       '# rows to insert

    On Error Resume Next
    Set tmp = Application.InputBox(prompt:="Select the range where you want to insert rows", _
    Title:="SELECTION", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    If tmp Is Nothing Then Exit Sub

FR = tmp(1).Row
CR = tmp.Rows.Count
LR = FR + CR - 1

    NR = Application.InputBox("Please enter the number of rows to insert", "# ROWS", Type:=1)
    If NR = False Then
    MsgBox "No rows will be inserted", 48, "Operation aborted"
    Exit Sub
    End If

Application.ScreenUpdating = False

Columns(1).Insert
Set rng = Range(Cells(FR, 1), Cells(LR, 1))

    With rng
    Cells(FR, 1) = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
    Rows(LR + 1 & ":" & LR + NR * CR).Insert Shift:=xlDown
    .Copy .Offset(CR, 0).Resize(CR * NR, 1)
    .Resize(CR * (NR + 1)).EntireRow.Sort Key1:=Cells(FR, 1), Order1:=xlAscending, Header:=xlNo
    .EntireColumn.Delete
    End With

Application.ScreenUpdating = True

End Sub

thanks
steve

 

[RoryA]

Can you put an apostrophe in front of the On Error Resume Next line, and then run the code and tell us what happens?

 

[steve hill]

Can you put an apostrophe in front of the On Error Resume Next line, and then run the code and tell us what happens?

hi Rory
tried this and got a object not required error box

steve

 

[RoryA]

How are you selecting the range in question? It would seem to be a problem with the range as you suspected.

 

[steve hill]

How are you selecting the range in question? It would seem to be a problem with the range as you suspected.

Hi
I am clicking on the first line and dragging down to the last line

steve