Macro Copy Cells

Posted by Andonny on January 25, 2001 3:25 PM

Hi,
I am hoping to find a kind person who would modify the code below for me.
It copies the cells between "Area 2 Start" and "Area 2 Finish" from sheet1 to sheet2.
What I would like to change is that it copies only the cells up to Culumn F.
At the moment it copies all culumns across. I have cells with data on sheet2 after culumn F which I don't want to disturb.

Thank you very much
Andonny

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

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

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

Posted by Tim Francis-Wright on January 25, 2001 6:38 PM

I think that this does what you want:

Sub Copy()
Dim Area2St As String, Area2Fn As String, Copythis As Range
With Sheets("sheet1")
Area2St = .Cells.Find(What:="Area 2 Start", searchorder:=xlByRows).Address
MsgBox Area2St
Area2Fn = .Cells.Find(What:="Area 2 Finish", searchorder:=xlByRows).Address
Set Copythis = Intersect(.Columns("A:F"), .Range(Area2St & ":" & Area2Fn))
Copythis.Copy Sheets("Sheet2").Range("A2")
End With
End Sub

Hope this helps.

Posted by Dave Hawley on January 25, 2001 6:42 PM

Hi Andonny

Couldn't you simply change: Area2Fn = "D"

To: Area2Fn = "F"

Dave

OzGrid Business Applications