having problem with resize

[Logan]

Having problem with the following formula.

Sub CopyRow()
Dim lRows As Long

With Range("Database")
lRows = .Rows.Count + 1
Range("Input").Copy Destination:=.Cells(lRows, 1)
.Resize(lRows).Name = "Database"
End With

Application.Goto Reference:="level"

If Range("position").Value = "Player" And
Range("level").Value = "MIDGET" Then


With Range("MIDGET")
lRows = .Rows.Count + 1
Range("name").Copy Destination:=.Cells(lRows, 1)
.Resize(lRows).Name = "MIDGET"
End With

ElseIf Range("position").Value = "Pitcher" And
Range("level").Value = "MIDGET" Then


With Range("MIDGETPITCHER")
lRows = .Rows.Count + 1
Range("name").Copy Destination:=.Cells(lRows, 1)
.Resize(lRows).Name = "MIDGETPITCHER"
End With

The problem is with resizing "MIDGETPITCHER"(B33). The
macro runs fine but I want it to place the "name" of the
player in B3 of named range "MIDGETPITCHER".
As it runs now all I get added each time is the column 'B'
added. I would like B added to each new player added.

any ideas what I'm doing wrong?

 

[Vasant Nanavati]

Not tested, but perhaps the pasting is messing up the range. Have you tried
expanding the range first and then pasting? Or pasting just the values using
PasteSpecial?