Runtime error 1004 when copying column to other workbook

[cg85cg]

I am relatively new to VBA and have run into a problem i can not solve through tinkering of the code or searching of the web.

Run-time error '1004':
Application-defined or object-defined error

To summarize. I am trying to copy a column of undefined length starting at cell A1 from one workbook, to another workbook starting at a known cell.

The variables may need clarifying, here is an index

prefix$-------used to define the workbook
currentdate,--used to define the workbook
year----------used to define the workbook

numrows----the number of used rows on the workbook being
copied to. I am trying to copy the column to the
workbook at cell(numrows + 1, A)

I have done everything in know to do to get this code to function properly

the problem section is pasted below. I will provide more code if necessary. The line in red generates the error

numrows = Workbooks("ML_" & year & ".xls").Worksheets(1).Rows.Count
Workbooks(prefix$ & currentdate & ".xls").Worksheets(1).Range("A").Copy
Workbooks("ML_" & year & ".xls").Worksheets(1).Cells(numrows + 1, 1).Paste

 

[Gavona]

When you get an error you don't understand, choose to debug and Add Watches so that you can see the values of the various expressions in the statement giving the error message. I find adding .address to the watch expression helpful.
Workbooks("ML_" & year & ".xls").Worksheets(1).Cells(numrows + 1, 1).address for example.

faq707-4594 should help.

Quick thought, there is not enough space to copy a whole column starting several rows down the target workbook. If you tried doing the operation manually using the ranges from the watch window you would probably get a more helpful error message. You would certainly come to realise if the code or the concept is at fault.

If there is nothing in the adjacent columns and there are entries in every row then Range("a1").currentregion.copy might get you there.


Gavin

 

[cresbydotcom]

Another thought - it is easier to define the destination explicitly - and in this case maybe select the whole row where you wish to insert.
eg

Workbooks("ML_" & year & ".xls").Worksheets(1).Rows(numrows + 1 & ":" & numrows + 1).Select
Workbooks("ML_" & year & ".xls").Activate
Workbooks("ML_" & year & ".xls").Worksheets(1).Select
ActiveSheet.Paste

but it does leave the active workbook and sheet not in the calling workbook so you have to get the focus back to where you need it. I am going from memory but look at the Activate and Select methods for a better picture.

 

[combo]

In your case numrows=65536, i.e. number of all rows in worksheet. Cells(numrows + 1, 1) does not exist.

combo

 

[cg85cg]

So, Combo, the Rows.Count function counts all the rows in the worksheet? Should I write a seperate loop to count the rows manually or is there a function I can use to count the used rows only?

 

[SkipVought]

Hi,

Try narrowing it to the UsedRange

numrows = Workbooks("ML_" & year & ".xls").Worksheets(1)[b].UsedRange[/b].Rows.Count
    Workbooks(prefix$ & currentdate & ".xls").Worksheets(1).[red][b]Range("A")[/b][/red].Copy
    Workbooks("ML_" & year & ".xls").Worksheets(1).Cells(numrows + 1, 1).Paste

But Range("A") is a problem.

You cannot mean the entire column, because you could not paste the entire column at some row in the other workbook.

What RANGE did you want to copy?

Skip,

_[red][/red]

 

[cg85cg]

Thank you all for your suggestions. I ended up solving the problem by more explicitely defining ranges to be copied instead of columns, and by being more sequential when pasting.
book1.sheet.range.copy
book2.sheet.range.paste