Solved: Copy range from one sheet to another

[akamax_power]

Ok, I want to be able to copy a range from Sheet1 starting at 1 row below the cell in column "A" that contains the value "4,0,0" and everything inbetween to the cell in Colulmn "A" that contains the value "5,0,3" and then insert that range in Sheet2 right above the cell in column "A" that contains the value "5,0,0"

Thanks

[mdmackillop]

[VBA]Sub Copies()
Dim Start As Range, Endd As Range, Tgt As Range
Set Start = Sheets(1).Columns(1).Find("4,0,0").Offset(1)
Set Endd = Sheets(1).Columns(1).Find("5,0,3")
Set Tgt = Sheets(2).Columns(1).Find("5,0,0")
Range(Start, Endd).EntireRow.Copy
Tgt.Insert
Application.CutCopyMode = False
End Sub
[/VBA]

[akamax_power]

I get a method range failed error on the line:

Range(Start, Endd).EntireRow.Copy

[mdmackillop]

Step though the code to find the problems. What results for Start and Endd. I've no Excel access for a couple of weeks so can't really assist. It did work in my test workbook though.

[akamax_power]

OK so i figured out the problem, I put the code as a worksheet function and not in a module. But now i'm trying to make sheet 1 and sheet 2 variables based on a listbox and an activesheet. I get an object required error as soon as it attempts to identify the first string. I don't know what I'm missing. Here's the code:

[VBA]Sub copies()

Dim Start As Range, Endd As Range, Tgt As Range
Dim i As Integer
Dim insert, tpl As String



i = Mid(Range("A" & target.Row), 7, 1)
insert = "5,0,3," & i
tpl = Range("L" & target.Row)


Set Start = Sheets(tpl).Columns(1).Find("4,0,0").Offset(1)
Set Endd = Sheets(tpl).Columns(1).Find("5,0,3").Offset(-1)
Set Tgt = ActiveSheet.Columns(1).Find(insert)
Range(Start, Endd).EntireRow.copy
Tgt.insert
Application.CutCopyMode = False

End Sub[/VBA]

[mdmackillop]

1. Use Option Explicit. This will show that "target" is not declared
2. As target has not been set to a range, target.row has no value
3. Insert is an instruction, not a variable. It should not be declared

[akamax_power]

Alright that works, i didn't realize i used insert as a variable. In working with this now I want to change the value of a range of cells from the copy from sheet before the entire range is copied. However if i add the line:
[VBA]Sheets(tpl).Range(start,endd).Value = "4,1,1"[/VBA]
I get an object not defined error.

[mdmackillop]

Start and Endd already include the sheet location (see post 2) , so try

[vba]

Range(start,endd).Value = "4,1,1"
[/vba]

[akamax_power]

Nevermind, I'm partly retarded. I forgot that the sheet i was trying to change is protected. The code you gave me was what i had originally but I was still getting the error. I just added the unprotect command before the code and it works correctly now.

thanks again