Copy Macro

Posted by Andonny on January 31, 2001 3:05 AM

Hi,
I would like to achieve with the code below that O_start and O_end are declared the correct way and then I would like to use them in the two lines below correctly.
(What:="O_start", After:=.Cells(1, 1)).Row
(What:="O_end", After:=.Cells(1, 1)).Row

Thank you very much for your help in advance
Andonny

Below is the whole macro:

Sub Copy()

Dim mysheet As Worksheet
Set mysheet = ActiveSheet

Dim Area2St As String, Area2Fn As String
Dim O_start As String
Dim O_end As String

O_start = mystring.Range("Q16")
O_end = mystring.Range("Q17")

With Sheets("Sheet1").Cells
' starts copying at Area2St = "A" where A represents column A
Area2St = "A" & .Find _
(What:="O_start", After:=.Cells(1, 1)).Row
' Copies up to Area2Fn = "G" where G represents column G
Area2Fn = "R" & .Find _
(What:="O_end", After:=.Cells(1, 1)).Row

.Range(Area2St & ":" & Area2Fn).Copy _
mysheet.Range("A2")
End With

End Sub

Posted by Dave Hawley on January 31, 2001 3:34 AM

Hi Andonny

Try this:

Sub Copy()

Dim mysheet As Worksheet
Set mysheet = ActiveSheet

Dim Area2St As String, Area2Fn As String
Dim O_start As String
Dim O_end As String

O_start = mystring.Range("Q16")
O_end = mystring.Range("Q17")

With Sheets("Sheet1").Cells
' starts copying at Area2St = "A" where A represents column A
Area2St = "A" & .Find _
(What:=O_start, After:=.Cells(1, 1)).Row
' Copies up to Area2Fn = "G" where G represents column G
Area2Fn = "R" & .Find _
(What:=O_end, After:=.Cells(1, 1)).Row

.Range(Area2St & ":" & Area2Fn).Copy _
mysheet.Range("A2")
End With

End Sub

I'm a bit confused by "mystring" has this been set to a Worksheet ?

Dave
OzGrid Business Applications

Posted by Andonny on January 31, 2001 3:55 AM

Hi,
Thank you for your kind reply.

When I run the macro I get following:

Run-time error '424':
Object required

And also when I go to debug it highlights following line in yellow:
O_start = mystring.Range("Q16")

Sorry about that
Andonny

Posted by Dave Hawley on January 31, 2001 4:05 AM

Hi Andonny

So what is "mystring" ?

If it is a object variable you will need to set it something like:

Dim mystring as Worksheet
Set mystring=Sheet5

Dave
OzGrid Business Applications

Posted by Andonny on January 31, 2001 4:46 AM

Hi Dave,
My apology this was my fault. It shoud have read "mysheet" instead of "mystring".
It works perfectly now with your kind help.

Thanks a million
Andonny

Posted by Dave Hawley on January 31, 2001 4:54 AM

Always happy to help :O)
OzGrid Business Applications