-1

I'm pretty new to VBA and have a question regarding some code. The code below is just section of code from a Excel macro. I've read many places about removing the .Select and .Activate commands as these slow down the process. Could someone let me know how I would do that in this case? Many thanks

Sheets("Sheet1").Activate
Set output = Range("U139")
Set Name = Range("U6")

Sheets("PPR Data").Select
Set myrange = Range("C:E")
output.Value = Application.WorksheetFunction.VLookup(Name, myrange, 3, False)

Sheets("Sheet1").Select
Set output = Range("W139")
Set Name = Range("W6")

Sheets("PPR Data").Select
Set myrange = Range("C:E")
output.Value = Application.WorksheetFunction.VLookup(Name, myrange, 3, False)

Sheets("Sheet1").Select
Set output = Range("X139")
Set Name = Range("X6")

Sheets("PPR Data").Select
Set myrange = Range("C:E")
output.Value = Application.WorksheetFunction.VLookup(Name, myrange, 3, False)

Sheets("Sheet1").Select
Set output = Range("V139")

Sheets("PPR LP").Select
output.Value = Application.WorksheetFunction.Max(Range("C:K"))
Chris
  • 21
  • 1
  • 1
  • 2

1 Answers1

-1

First of all declare a new worksheet variable as such:

Dim myWorksheet as Worksheet

Then assign each sheet to this variable whenever you are dealing with a Worksheet. You could also create multiple Worksheets to make your code clearer.

Set myWorksheet = Sheets("Sheet1")

Now that you are dealing with a Worksheet object, you need to refer to the Range in that Worksheet.

Set output = myWorksheet.Range("U139")
Set Name = myWorksheet.Range("U6") 'I would suggest using another name for this variable.

Keep in mind sometimes it can be necessary to activate a Worksheet, such as in cases of pasting.

Cheers.

Lorne
  • 181
  • 2
  • 11