Proper VBA code in Excel - Selecting cells

I am trying to figure out how to copy a dynamic range on another sheet to paste into the current sheet.  The range would be cell A1 and extended to the right.  I can make it happen with Select statements, but that's not the right way.  Just trying to figure out the "best" way without Select.

This is the code that works:
    Sheets(2).Select
    Range("a1").Select
    Range(ActiveCell, ActiveCell.End(xlToRight)).Copy

Open in new window


This does not (I get  an Object Required error on the second line):
    Locat = Sheets(2).Range("a1").Address
    Range(Locat, Locat.End(xlToRight)).Copy

Open in new window


This also does not work -I get a Compile error Expected: List seperator or ) and it highlights the period beforee end
Sheets(2).range("a1", "a1".End(xlToRight)).Copy

I know I am just doing one thing wrong, but can't find it.  Thanks in advance.
pmpataneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nutschCommented:
Try this:

with Sheets(2)
.range(.cells(1,"A"), .cells(1,"A").End(xlToRight)).Copy
'or .range(.cells(1,1), .cells(1,1).End(xlToRight)).Copy
'or Sheets(2).range(.[a1], .[a1].End(xlToRight)).Copy
end with

Open in new window


Thomas
0
FamousMortimerCommented:
Or

Sheets(2).Range("A1:A" & Sheets(2).Range("A1").End(xlToRight).Row).Copy
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pmpataneAuthor Commented:
Thanks FamousMortimer...I really like that solution as well...more in line with my thinking when I code
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

FaustulusCommented:
@pmpatane
Change your way of thinking.
You asked for the "best" way. Look at nutsch' line 3.

Remember that Excel is good at numbers. Therefore it gives numbers to its rows and columns, not letters. Cell(1, 1) requires less translation that Cells(1, "A") which requires less translation that Range("A1").
From the viewpoint of efficiency Range("A1:A" & Sheets(2).Range("A1").End(xlToRight).Row) definitely is worse than any of the above.

If you want fast and efficient code use the syntax least taxing for Excel's resources.
0
FamousMortimerCommented:
You are really splitting hairs.  This is one operation, not billions.  Although a nano second or two might be saved, it is easier to read the code so at that point it becomes the preference of the programmer.
0
pmpataneAuthor Commented:
@FamousMortimer, I made one slight change as I am copying A1 to the right
Sheets(2).Range("A1:" & Sheets(2).Range("A1").End(xlToRight).Address).Copy

Open in new window


@Faustulus, I understand what you're saying about Excel liking numbers and having to "convert" numbers to letters (e.g. the COLUMN function, etc.) but this code is from 8 lines of code and it is just combining the data from all sheets into one without doing any calculations on the data.  Lots of copying and pasting and I was making sure to avoid Select statements as those are ridiculous time wasters.

Thank you both for your input (and nutsch as well)!  I like learning and seeing many different ways to accomplish the same goal
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.