Run Excel macro without opening other sheets which are referred

Question

I have below code to copy and paste from different sheets in workbook. I don't want to show the user different sheets opening and show only the sheet in which the action triggered (Interface).

How can I do it, any guidance?

Sheets("List").Select
Range("N2").Select

lastrow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Selection.AutoFill Destination:=Range("N2:N" & lastrow)

Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O" & lastrow)

Range("O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("Team Sports Pricelist").Select
Range("N5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
Sheets("Interface").Select

You don't need to activate a sheet or select a cell in order to access either. If you don't `Activate` and don't `Select` the display won't change. — Variatus, Nov 26 '20 at 00:31
^^^ [Here's how](https://stackoverflow.com/q/10714251/445425) — chris neilsen, Nov 26 '20 at 01:05

Variatus · Accepted Answer · 2020-11-26T02:32:14.363

This is what your code might look like if you don't select anything. (Code isn't tested).

Dim Ws      As Worksheet
Dim Rng     As Range

Set Ws = Worksheets("List")
Set Rng = Ws.Range(Ws.Cells(2, "N"), Ws.Cells(Ws.Rows.Count, "N").End(xlUp))
Rng.Cells(1).AutoFill Destination:=Rng
Set Rng = Rng.Offset(0, 1)
Rng.Cells(1).AutoFill Destination:=Rng

Rng.Copy
Worksheets("Team Sports Pricelist").Range("N5").PasteSpecial xlPasteValues

Run Excel macro without opening other sheets which are referred

1 Answers1