VBA code to not have sheet array selected?

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
Below is the end of a macro, unless it hits an error but the end of the error handler looks like this as well. This code saves two worksheets as a PDF. The problem is sometimes the users don't remember to only have it on Worksheet "A" after using this. To clarify, this worksheet is a template for reports. So they fill out the information, use the macro to copy the information into the correct workbooks, then can either hit a button deleting all of user entered information essentially resetting the worksheet or they can just type over the user entered information. If they don't make sure only Worksheet A is selected when they start typing and run the macro again some weird stuff happens. I've read you should avoid using the select method whenever possible. So what would be the appropriate way to only select sheet A at the end of this?

VBA Code:
'   Export source workbook to PDF
    With srcWB
        fName = srcWB.Sheets("A").Range("A!F19").Value
                Sheets(Array("A", "Sheet2")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Asphalt\Asphalt Reports\" & fName, _
        openafterpublish:=True, ignoreprintareas:=False
    End With

Exit Sub
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
I've read you should avoid using the select method whenever possible. So what would be the appropriate way to only select sheet A at the end of this?
That advice usually applies to wanting to apply a bunch of actions. However, if your goal is to "select" a particular sheet, then selecting it is the way you would do that.

Note: That the main reason to usually avoid using "select" statements they are often unnecessary, and can slow your VBA performance down (especially if they are used within a loop). However, if you just want to select one particular sheet at the end of a macro, that is really going to have a neglible effect on performance (a single select is pretty insignificant).

So that is something I wouldn't really worry about.
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

Where I see people get in most trouble is when they have things like this either in a loop, or have a whole bunch of statements like these in succession:
VBA Code:
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
instead of just writing this:
VBA Code:
    Range("F1").FormulaR1C1 = "=RC[-2]+RC[-1]"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top