0

I have this partial code. 

  ActiveSheet.Range("A1:B150").Copy
  Milestone.Sheets("Transit").Activate
  ActiveSheet.Paste
  TransitFile.Close

  'Vlookup L4 Names
  Range("F2").Formula = "=1+5"

"Transit" is the second worksheet in my workbook. In the last line (the formula line), it inputs the formula into the first sheet of my workbook (Sheet1). I don't understand why is that when the "Transit" sheet is the one that is Active. Can anybody help me explain why?

And how come I don't need to use ActiveSheet in Range("F2").Formula but I need to if I use select as in ActiveSheet.Range("A2:B4").Select

TorontoUser
  • 111
  • 2
  • 10

1 Answers1

1

To avoid mistakes you can use sheets("Name").range. If you simply use range the program will assume you want the range in the active sheet.

For example, 

Sheets("Feuil1").Select
Range("A1:B150").Copy

works as well as 

Sheets("Feuil1").Select
ActiveSheet.Range("A1:B150").Copy

The best thing to try is to use Sheets("Feuil1").Range("A1:B150").Copy so excel knows which range of which sheet to use

Hearner
  • 2,711
  • 3
  • 17
  • 34
  • It is generally frowned upon to use .Select http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros They also frown on .Activate but I have been using that for years without any issues. – MatthewD Aug 21 '15 at 14:12
  • So in my code, "Transit" is the active sheet. But it's pasting the formula (last line) in my first worksheet ("Sheet1"). Why is that? Sheet1 is not the active sheet, right? – TorontoUser Aug 21 '15 at 14:21
  • If i understand well, it's "Transit". Go in your macro and go execute your program step by step and you'll see which sheet is used and everything (F8) – Hearner Aug 21 '15 at 14:24