When I try and copy a selection of cells my code throws the following code

Runtime Error 424: Object Required

Any Ideas? Here is part of my code

For a = 1 To ACount
mainworkbook.Activate
Sheets("List").Cells(a*20, 1).Select
Range(ActiveCell, ActiveCell.Offset(-19, 5)).Select
Selection.Copy ' THIS IS WHERE THE ERROR OCCURS
secondaryworkbook.Activate
Sheets(a).Cells(1, 1).Select
Selection.Paste
Next

Hi,

you don't need to select the range to copy/paste... I think this code would
also do the same thing as your code:

For a = 0 To ACount - 1
Set rngCopy = mainworkbook.Worksheets("List").Cells(1, 1).Offset(a * 20)
rngCopy.Resize(20, 5).Copy
Destination:=secondaryworkbook.Worksheets(CStr(a + 1)).Cells(1, 1)
Next a



--
Hope that helps.

Vergel Adriano


"the00snoopy" wrote:

When I try and copy a selection of cells my code throws the following code

Runtime Error 424: Object Required

Any Ideas? Here is part of my code

For a = 1 To ACount
mainworkbook.Activate
Sheets("List").Cells(a*20, 1).Select
Range(ActiveCell, ActiveCell.Offset(-19, 5)).Select
Selection.Copy ' THIS IS WHERE THE ERROR OCCURS
secondaryworkbook.Activate
Sheets(a).Cells(1, 1).Select
Selection.Paste
Next

Thanks. I had to take out the CStr command so that the line was

Destination:=secondaryworkbook.Worksheets(a + 1).Cells(1, 1)

before it would work but after that it worked great.

Just for furture refrence does anybody know why I had error before?

"Vergel Adriano" wrote:

Hi,

you don't need to select the range to copy/paste... I think this code would
also do the same thing as your code:

For a = 0 To ACount - 1
Set rngCopy = mainworkbook.Worksheets("List").Cells(1, 1).Offset(a * 20)
rngCopy.Resize(20, 5).Copy
Destination:=secondaryworkbook.Worksheets(CStr(a + 1)).Cells(1, 1)
Next a



--
Hope that helps.

Vergel Adriano


"the00snoopy" wrote:

When I try and copy a selection of cells my code throws the following code

Runtime Error 424: Object Required

Any Ideas? Here is part of my code

For a = 1 To ACount
mainworkbook.Activate
Sheets("List").Cells(a*20, 1).Select
Range(ActiveCell, ActiveCell.Offset(-19, 5)).Select
Selection.Copy ' THIS IS WHERE THE ERROR OCCURS
secondaryworkbook.Activate
Sheets(a).Cells(1, 1).Select
Selection.Paste
Next