Solved: I'm having trouble selecting a range

[EirikDaude]

I'm making a macro for (among other things) copying a selection of cells from one worksheet to another. According to the VBA-help it seems the method I'm using should work, but it seems that when I try to select the range I want to copy the "Cells()"-function returns the contents of the cell instead of the address. I've also tried using Cells().Address, but I didn't get that to work either.

Have any of you guys got any idea of a different approach I can use to acchieve the same result?

I'm using Excel 2003 with SP3 and Visual Basic 6.5.

Here's the code I've come up with:
[vba]For i = LBound(skift1MF) To UBound(skift1MF)
Sheets("Kompetanseoversikt").Range(Cells(skift1MF(i), 1), Cells(skift1MF(i), 8)).Select
Selection.Copy
Sheets("Kompetanseoversikt (2)").Range(Cells(5 + i, 1), Cells(5 + i, 8)).Select
ActiveSheet.Paste
Next[/vba]

[Bob Phillips]

[vba]

Set sh = Sheets("Kompetanseoversikt (2)")

With Sheets("Kompetanseoversikt")

For i = LBound(skift1MF) To UBound(skift1MF)

.Range(.Cells(skift1MF(i), 1), .Cells(skift1MF(i), 8)).Copy _
sh.Range(sh.Cells(5 + i, 1), sh.Cells(5 + i, 8))
Next
End With
[/vba]

[EirikDaude]

Thanks for the help!

It seems that solved my problem. But could you please tell me why my attempt at defining the range failed while your worked. I'm curious

I'd also like to know if I was correct in defining "sh" as an object-variable (I was asked to define the variable before I was allowed to set any properties for it).

[Bob Phillips]

You were not qualifying all of the range objects (Range, Cells), and so it was reflecting the activesheet in those cases, not the one you thought.