PDA

View Full Version : Solved: Copy range from one sheet to another



akamax_power
06-12-2008, 04:24 PM
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
06-12-2008, 04:50 PM
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

akamax_power
06-13-2008, 02:29 PM
I get a method range failed error on the line:

Range(Start, Endd).EntireRow.Copy

mdmackillop
06-15-2008, 11:46 AM
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
06-16-2008, 10:01 AM
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:

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

mdmackillop
06-23-2008, 05:34 AM
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
06-23-2008, 10:28 AM
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:
Sheets(tpl).Range(start,endd).Value = "4,1,1"
I get an object not defined error.

mdmackillop
06-24-2008, 08:14 AM
Start and Endd already include the sheet location (see post 2) , so try


Range(start,endd).Value = "4,1,1"

akamax_power
06-24-2008, 01:08 PM
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