having problem with resize
 

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"(B3:P3). 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:P added to each new player added.

any ideas what I'm doing wrong?

having problem with resize
 

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?

--

Vasant


"Logan" wrote in message
...
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"(B3:P3). 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:P added to each new player added.

any ideas what I'm doing wrong?