asked on

Selection not working

Hi,

I have a piece of code to Count rows through Col A and Select Columsn A:M and Copy the selection but it dopes not seem to work, does it look okay?

Thanks
Seamus

With Sheets("Outstanding Items Report-1")
r = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A1:M" & r).Copy Workbooks("Asset Services Outstanding Stock Items.xls").Sheets("Outstanding Items Report-1").Cells(Rows.Count, "E").End(xlUp).Offset(1, -4)
End With

Tracy

Try this:

With Sheets("Outstanding Items Report-1")
r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A1:M" & r).Copy Workbooks("Asset Services Outstanding Stock Items.xls").Sheets("Outstanding Items Report-1").Cells(Rows.Count, "E").End(xlUp).Offset(1, -4)
End With

StephenJR

Seamus - does the code do anything, and if so what?

Seamus2626

ASKER

Im trying to get ito to copy the selection A1:M and down (using E as the count)

Then i will paste it

Thanks
Seamus

ASKER CERTIFIED SOLUTION

Tracy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

Sorry Broomee9, was just testing yours, all good!

Thanks
Seamus

StephenJR

Ah missed that, though I don't understand why that should have caused a problem.

Tracy

No worries, I've missed putting in the dots in my own code sometimes too. It can happen to anyone. :-)

Tracy

>>Ah missed that, though I don't understand why that should have caused a problem.

Without the dot, the Rows.Count refers to the ActiveSheet, so if you're not on the proper sheet when you run the code, then it will get the number of rows on the wrong sheet. The dot (.) ties it to the specified sheet, so it doesn't matter what sheet you're on when you run the code.

StephenJR

But I would have thought (clearly wrongly) that Rows.Count wouldn't change according to the sheet. Isn't it 65k.

Tracy

Yes it is, but for the ActiveSheet, so it's A65536 on the ActiveSheet.

So if you have 50 rows of data in your desired sheet and 100 rows of data in your ActiveSheet r will return 100 without the dot and 50 with the dot.

StephenJR

broomee9: thanks for sticking with me! I would have thought the first dot would cover that, but I think I see what you are saying.