Macro falls over at the end

You should be able to fix the errors by using

ActiveSheet.Range("A1:G10").Select instead
of ActiveCELL.Range...

Paul -

Playing the macro, the line:

Selection.End(xlDown).Select

got me to the last row of the spreadsheet, Row 65536. Since the offending line of code is trying to select, relatively, the last row and the next nine rows after that, you're code is falling off the edge of the earth, from Excel's perspective. I believe that's the source of your run-time error.

The code works if your offending line is, say,
ActiveCell.Range("A1:A10").Select
since you are staying within the spreadsheet's limited universe.
Tom

It won't work

HI guys,

I've tried both methods, but alas the code has other ideas.

Changing ActiveCell to ActiveSheet made it alternate a sheet when extracting data and pasting it on a different workbook. Then it still came up with the error.

Changing A1:G10 to A1:A10 only selected part of the data. The data is from A1 to G10.

Is there another way? other wise I'll have to re-haul the code!

Cheers,

Paul

Re: It won't work

Paul
Which workbook is your active one.
The one with this code running ??

Ivan

Re: It won't work

The Macro Code that is listed is started from another workbook via a Button.

However wb1 & wb2 contain the data that needs to be extracted, the amount of data may vary - hence no stop code(?).

Wb3_new is the workbook that has all the data pasted on indivdual sheets (until no more data).

So I persume that the active one is the one with the code running.

Excuse my ignorance but VBA is still baffling to me.

Thank you if you can help.

Paul.

Re: It won't work

Paul
I think you probably need a Do....Loop.
Try the following :-

Sub YourMacro()
Do
Windows("wb1.xls").Activate
Application.Goto Reference:="R1C1"
Selection.End(xlDown).Select
If ActiveCell = Range("A65336") Then Exit Do
ActiveCell.Range("A1:G10").Select
Selection.Cut
Windows("WB3_New.xls").Activate
Sheets.Add
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveSheet.Paste
Windows("wb2.xls").Activate
Application.Goto Reference:="R1C1"
Selection.End(xlDown).Select
ActiveCell.Range("A1:G10").Select
Selection.Cut
Windows("WB3_New.xls").Activate
ActiveCell.Offset(11, 0).Range("A1").Select
ActiveSheet.Paste
Loop
Application.Run "Extract_Click"
Application.Run "Extract_Click"
Windows("wb1.xls").Activate
ActiveWindow.Close
Windows("wb2.xls").Activate
ActiveWindow.Close
Range("A1").Select
End Sub

Celia

Re: It won't work

Paul
Is this code the complete code ??
because if it is then you will need to add
@ the start Windows("wb1.xls").Activate

or has this workbook already been activated
from previous code ?? because as given, if your
calling routine is from another workbook then
your first line works on this workbook AND if
you have NO data in the column then Toms comments
apply and you will get this error.
If so then just add the line;

Windows("wb1.xls").Activate
then the rest of your code as given.

Ivan

New prob

Celia,

I'm sorry to say that it now comes up with a new message; "Run time error'28' Out of stack space"
It then highlights in yellow "Application.Run "Extract_Click""

Do you know how to avoid this?

Cheers,

Paul.

Re: New prob

Paul
What does the macro Extract_Click do ?
Perhaps you'd better post the code.
Celia

All fine

Everyone big thanks.

Sorted prob.

Paul