SOLVED

Need help with writing a more efficient line of code

%3CLINGO-SUB%20id%3D%22lingo-sub-2828041%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20writing%20a%20more%20efficient%20line%20of%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828041%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20workbook%20with%20several%20sheets%20I'm%20working%20on.%26nbsp%3B%20I%20need%20help....I%20want%20to%20set%20a%20formula%20on%20one%20of%20the%20sheets%20without%20having%20to%20select%20that%20worksheet....that%20way%20I%20don't%20have%20flashing%20between%20sheets%20when%20the%20VBA%20code%20runs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3ESheet%201%20%3D%20Workflow%20Summary%3C%2FP%3E%3CP%3ESheet%202%20-%20PFI%20(this%20is%20the%20sheet%20from%20which%20I%20am%20running%20the%20code)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E'assume%20I%20am%20running%20this%20while%20on%20the%20PFI%20sheet%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3ESheets(%22WorkFlow%20Summary%22).Select%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3ERange(%22J4%22).Select%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3EActiveCell.Formula%20%3D%20%22%3DPFI!J2%22%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20run%20this%20from%20the%20PFI%20sheet%2C%20the%20code%20flashes%20to%20the%20WorkFlow%20Summary%20sheet%20and%20then%20back%20to%20the%20PFI%20sheet.%26nbsp%3B%20It%20looks%20awful%20when%20you%20have%20a%20bunch%20of%20these%20that%20you%20need%20to%20run%20at%20the%20same%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%26nbsp%3B%20I%20would%20love%20to%20just%20say%26nbsp%3B%3CFONT%20color%3D%22%230000FF%22%3ESheets(%22WorkFlow%20Summary%22).Range(%22J4%22).ActiveCell.Formula%20%3D%22%3DPFI!J2%22%20%3CFONT%20color%3D%22%23000000%22%3E%2C%20but%20that%20doesn't%20work%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2828041%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828388%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20writing%20a%20more%20efficient%20line%20of%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828388%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20not%20quite%20sure%2C%20if%20I%20understood%20it%20correct%2C%20but%20you%20can%20stop%20screen%20updating%20by%20using%20Application.ScreenUpdating%20%3D%20False%20at%20the%20start%20of%20your%20VBA%20code%20and%20then%20turn%20it%20back%20on%20again%20right%20before%20the%20end%20of%20your%20code%20by%20using%20Application.ScreenUpdating%20%3D%20True.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20beware%2C%20that%20you%20will%20not%20be%20able%20to%20see%20what%20the%20macro%20is%20doing.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFor%20further%20reading%20see%20the%20documentation%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Fexcel.application.screenupdating%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Fexcel.application.screenupdating%3C%2FA%3E%3CBR%20%2F%3EHope%20it%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828441%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20writing%20a%20more%20efficient%20line%20of%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828441%22%20slang%3D%22en-US%22%3EYou%20rock!%20Works%20perfect.%20Thank%20you.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have an workbook with several sheets I'm working on.  I need help....I want to set a formula on one of the sheets without having to select that worksheet....that way I don't have flashing between sheets when the VBA code runs.

 

Example:

Sheet 1 = Workflow Summary

Sheet 2 - PFI (this is the sheet from which I am running the code)

 

'assume I am running this while on the PFI sheet

Sheets("WorkFlow Summary").Select
Range("J4").Select
ActiveCell.Formula = "=PFI!J2"

 

When I run this from the PFI sheet, the code flashes to the WorkFlow Summary sheet and then back to the PFI sheet.  It looks awful when you have a bunch of these that you need to run at the same time.

 

EDIT:  I would love to just say Sheets("WorkFlow Summary").Range("J4").ActiveCell.Formula ="=PFI!J2" , but that doesn't work

 

Thanks!!!

2 Replies
best response confirmed by CLAKES (Occasional Contributor)
Solution

I'm not quite sure, if I understood it correct, but you can stop screen updating by using Application.ScreenUpdating = False at the start of your VBA code and then turn it back on again right before the end of your code by using Application.ScreenUpdating = True.

 

But beware, that you will not be able to see what the macro is doing.


For further reading see the documentation:
https://docs.microsoft.com/en-us/office/vba/api/excel.application.screenupdating
Hope it helps.

You rock! Works perfect. Thank you.