+ Reply to Thread
Results 1 to 3 of 3

VBA - Sheet Array with variable sheet name from cell value

  1. #1
    Registered User
    Join Date
    03-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    10

    VBA - Sheet Array with variable sheet name from cell value

    I have a command button that when pressed, saves a active sheet and an accompanying sheet from same workbook as a PDF. The code works great if I enter the non-active sheets name in the Sheet Array. However, I need the second (non-active sheet) to be variable, dependent on what is in a specific cell. Here is the code that works without the variable non-active sheet.


    Sub Save_Clear()
    '
    ' Save_Clear Macro
    ' Will save Form as PDF to AE file and Rest dropdowns to blank and clear content on row 3.

    Dim strFilename As String
    Dim rngRange As Range

    'Considering Sheet1 to be where you need to pick file name
    Set rngRange = Worksheets("Approval Form").Range("H5")

    'Create File name with dateStamp
    strFilename = rngRange.Value

    Sheets(Array("Approval Form", "VariableName")).Select

    Sheets("Approval Form").Activate

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\xyz\Desktop\Working\New\" & strFilename & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

    Sheets("VariableName").Select
    Sheets("Approval Form").Select

    Sheets("Approval Form").Unprotect
    Range("C8").Select
    Selection.ClearContents

    Range("D9").Select
    Selection.ClearContents

    Range("D10").Select
    Selection.ClearContents

    Range("A3:F3").Select
    Selection.ClearContents

    Sheets("Approval Form").Protect
    Sheets("VariableName").Select
    End Sub

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,936

    Re: VBA - Sheet Array with variable sheet name from cell value

    Use the Replace:=False option to extend the number of selected sheets instead of using the array method, where your variable name is in Sheet1 cell A2:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    10

    Re: VBA - Sheet Array with variable sheet name from cell value

    Thank you for your help. this worked perfect. Sorry for the delay in reply.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Insert a vlookup with variable array from another sheet.
    By Chris S. in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2013, 02:32 PM
  2. Replies: 1
    Last Post: 05-06-2013, 05:20 PM
  3. cell reference from one sheet to other sheet use variable
    By hassan1960 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-04-2013, 07:37 PM
  4. Link cell from variable named sheet to cell on another sheet with variable rows
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-07-2010, 01:38 PM
  5. [SOLVED] How to access muliple tabs of sheet as variable of an Array
    By GorKo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2006, 05:49 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1