• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 5
  • 4
  • 2
1 Solution
 
TracyVBA DeveloperCommented:
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
0
 
StephenJRCommented:
Seamus - does the code do anything, and if so what?
0
 
Seamus2626Author Commented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
TracyVBA DeveloperCommented:
What I pasted above works for me.  You are missing the dot (.) before the Rows.Count.
0
 
Seamus2626Author Commented:
Sorry Broomee9, was just testing yours, all good!

Thanks
Seamus
0
 
StephenJRCommented:
Ah missed that, though I don't understand why that should have caused a problem.
0
 
TracyVBA DeveloperCommented:
No worries, I've missed putting in the dots in my own code sometimes too.  It can happen to anyone. :-)
0
 
TracyVBA DeveloperCommented:
>>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.
0
 
StephenJRCommented:
But I would have thought (clearly wrongly) that Rows.Count wouldn't change according to the sheet. Isn't it 65k.
0
 
TracyVBA DeveloperCommented:
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.
0
 
StephenJRCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now