Run time error 1004

[paulageville]

Hi people,

I'm pretty new to vba and keep getting a run time error 1004 on the highlighted line when i run the following macro...

<code>
Sub auto_open()




Dim cwb As Workbook
Dim arow As Long
Dim brow As Long




Application.ScreenUpdating = False
Set cwb = ThisWorkbook
Sheets("Domestic Matrix").Activate
For Each Cell In ActiveSheet.UsedRange.Cells
If Cell.Value < Date - 365 Then
arow = cwb.Worksheets("Training Overdue").Range("A").End(xlUp).Row <<<<<<<<<<<
arow = arow + 1
cwb.Worksheets("Domestic Matrix").Range(ActiveCell.End(xlUp).Offset(1)).Copy
cwb.Worksheets("Training Overdue").Range("A" & arow + 3).PasteSpecial xlPasteValues
brow = cwb.Worksheets("Training Overdue").Range("B").End(xlUp).Row
brow = brow + 1
cwb.Worksheets("Domestic Matrix").Range(ActiveCell.End(xlLeft).Offset(0, 1)).Copy
cwb.Worksheets("Training Overdue").Range("B" & arow + 3).PasteSpecial xlPasteValues
Else
End If
Next
End Sub
</code>

basically I have a load of dates in a load of different cells.
What i want the macro to do is find each cell containing a date that is more than a year since current date.
For each such cell found, i want to paste the values from the 2nd cell in that row and the 2nd cell in that column into another worksheet (training overdue).
I hope that makes sense!
Not sure where I'm going wrong....

Please help!!

 

[tweedle]

Try this:

arow = cwb.Worksheets("Training Overdue").Cells(Rows.Count, "A").End(xlUp).Row

 

[starl]

<code>arow = cwb.Worksheets("Training Overdue").Range("A").End(xlUp).Row <<<<<<<<<<<

</code>You need a full cell address when using Range, such as Range("A1")
<code></code>Since you're returning the last used row on the sheet, you should do
<code>arow = cwb.Worksheets("Training Overdue").Range("A" & rows.count).End(xlUp).Row</code>

rows.count returns the number of rows on the ACTIVE sheet. So, the way I have it written assumes you have a single workbook in open. Else, you'll need to specifiy the workbook and sheet to ensure, if you are in 07 or newer, the code works right in a legacy or newer workbook.

 

[paulageville]

Thanks starl and tweedle,

Both solutions work great, however I am now getting the same error message on the code two lines lower down...

<code>
cwb.Worksheets("Domestic Matrix").Range(ActiveCell.End(xlDown).Offset(1, 0)).Copy
</code>

Any ideas?

 

[starl]

<code>
cwb.Worksheets("Domestic Matrix").Range(ActiveCell.address).End(xlDown).Offset(1, 0)).Copy</code>

Activecell is an object - a cell. It has an address, a value, color, fill, etc. It is equivalent to saying Range("A1")
You need to specify what you want. - the address.
Also, you were missing a )

 

[starl]

and when I think about it, you probably meant to replace Range with just ActiveCell
<code>
cwb.Worksheets("Domestic Matrix").ActiveCell.End(xlDown).Offset(1, 0)).Copy</code>