Range copying macro question

[Andy]

Hello,

I've been trying learn, very slowly, how to do some things with macros. The
book I have, as well as many of the posts I've read over, say to not Select
things, but to just act on the range or cells directly. I'm posting part of
a macro I've written with a couple of questions in the text lines.

Sheets("Prior_Rev3").Range("A:E").Copy
Sheets("Rev_Final").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

Sheets("Criteria").Select
'Range("C7:C7").Select
'M = ActiveCell.Offset(0, 0).Value
M = Range("C7").Value


Select Case M

Case "January"

Sheets("Prior_Rev3").Range("F:Q").Copy
Sheets("Rev_Final").Range("F1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Current_Rev3").Select
' These next lines do not work - WHY ?
' Sheets("Current_Rev3").Range("A2:E2", Range("A2:E2").End(xlDown)).Copy
' Sheets("Current_Rev3").Range("A2:E2").End(xlDown)).Copy
' On the next line of code, why is the ranged listed twice ?
Range("A2:E2", Range("A2:E2").End(xlDown)).Copy
Sheets("Rev_Final").Range("A1").End(xlDown).Offset(1, 0). _
PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Current_Rev3").Select
Range("F2", Range("F2").End(xlDown)).Copy
Sheets("Rev_Final").Range("F1").End(xlDown).Offset(1, 12). _
PasteSpecial xlPasteValues
Application.CutCopyMode = False

My questions are in the text lines of the January case. Basically, why does
the first line of the case work without having to select the sheet first, but
the following line has to have the sheet selected and why don't the two lines
that I've marked, work?

Sheets("Current_Rev3").Select
Range("A2:E2", Range("A2:E2").End(xlDown)).Copy
Sheets("Rev_Final").Range("A1").End(xlDown).Offset(1, 0). _
PasteSpecial xlPasteValues

Also, in some posts I've seen Destination:=

When is this used, as opposed to just tacking the sheet name on the front of
the line, as in

Sheets("Rev_Final").Range("F1").PasteSpecial xlPasteValues


I hope this is clear, as most of what I'm able to write is completely robbed
and reworked from the posts in this forum.

Thanks in advance,

Andy

 

[Jim Thomlinson]

Good questions. The biggest leap that you can make in yoru coding is to get
away from selecting things. It makes your code slower, longer and more prone
to errors. The next biggest thing is to refer directly to sheet instead of to
the sheet names (more on that later).

The XL Application is a container for workbooks. Workbooks are containers
for worksheets and worksheets are containers for ranges. When you write code
you do not need to specify the entire chain each time you want to reference
one of those objects. When you omit part of the chain a default is chosen.
The following lines of code are equivalent (assuming the code is in a
standard code module).

Range("A1").value
ActiveSheet.Range("A1").value

When you do the selects you are setting up the active sheet so you don't
need to worry about this. If you are not selecting then you need to be
specific.
If the same code was in a specific sheet then it is different. The sheet it
is in is the default and the active sheet has nothing to do with it.

Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Range("A1")

When in doubt be specific about where stuff is coming from otherwise it is
at the whim of what happens to be active at the time or where the code
resides.

When you are doing a copy you can either include the word Destination:= or
not. That is true for any method including sort and such. Copy is easy as it
has only one argument and that is the destination. Sort has many argument. If
you omit the headings from Sort then you need to get the argument values in
the correct order. If you include the headings then any order is fine. Offset
is the same and...

I said that the next big step is to refer dirctly to the sheets and not the
sheet names. In the VBE explorer you see Sheet1 (TabName). Sheet 1 is the
code name and Tab name is the name the user gave that tab. You can refer to
sheets directly by their code name. This makes code a lot easier and it keeps
code from crashing when the tab name gets changed.

Sheet1.Range("A1").Copy Destination:= Sheet2.Range("A1")

Note that intellisence works and you get the drop down. You can change the
code name of the sheet by changing the (Name) property.

 

[Andy]

Thanks very much Jim. This really helps alot.

-Andy