Copy Question

Posted by Andonny on December 25, 2000 8:14 PM

Hi,
I like to copy from 'area 2 start' to 'area 2 finish' (A5 : D9) to sheet 2.
Area 2 start does not always start at A5 and does not always finish at D9. It probably has to find 'area 2 start' first and then find 'area 2 finish' and then copy everything in between.
.......A...........B.......D
1..area 1 start
2................apple.....6
3................kiwi......7
4..area 1 finish
5..area 2 start
6................pear......3
7................orange....4
8................banana....2
9..area 2 finish
10.area 2 start
11...............pear......9
12...............orange....8
13...............banana....4
14.area 1 finish

I hope everybody has had a nice Christmas.
Thanks a lot
Andonny

Posted by Dave on December 26, 2000 4:23 PM

Hi Andonny

If Your Columns are always A:D then you could use some code like this

Sub MyCopy()
Dim Area2St As String, Area2Fn As String
With Sheets("sheet1").Cells
Area2St = "A" & .Find _
(What:="Area 2 Start", After:=.Cells(1, 1)).Row + 1

Area2Fn = "D" & .Find _
(What:="Area 2 Finish", After:=.Cells(1, 1)).Row - 1

.Range(Area2St & ":" & Area2Fn).Copy _
Sheets("Sheet2").Range("A1")
End With
End Sub

OzGrid Business Applications