Results 1 to 2 of 2

Macro to reference sheet other than active - unhide and/or export

Threaded View

Dirty Laundry Macro to reference sheet... 01-16-2009, 11:44 PM
mudraker Dirty Laundry Please take... 01-17-2009, 01:00 AM
  1. #1
    Registered User
    Join Date
    01-16-2009
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    5

    Question Macro to reference sheet other than active - unhide and/or export



    I have created a tool in Excel that allows a sales person to price a widget, view a Proposal (available on a separate tab via a macro), and then export this Proposal into a separate workbook so they can send it to their customer. No problem!

    On the Proposal tab, I tried to add more functionality to the "Export Proposal" macro. I would like to also export Material information that is located on a separate hidden tab. If that is not feasible, then I would like to simply unhide the tab. Then I'll just create a new macro on the Material tab to export that Material information.

    The following requirements have made this a challenging feat:
    • I'll need to use "IF / THEN / ELSE" statements to accomplish either scenario.
    • The "IF / THEN / ELSE" statements will be referencing a static cell that is on a separate hidden tab (Formula Data) that contains most of my "back end" formulas.
    • Also, I was hoping to be able to do some nesting within the "IF / THEN / ELSE" statements to include "OR" (please see example below).

    I have tried fooling around with this code for hours. I have searched online for answers. Then I realized that I just don't know enough yet, and I should ask for help instead of fruitlessly banging my head against the wall.

    Here's what I have so far (but I keep getting syntax and break mode errors):


    Sub Export_Proposal'
    ' Export_Proposal Macro
    ' Macro recorded 01/16/2009 by Dirty Laundry
    ' This macro exports quotes into a separate, customer-friendly workbook.
    
    ' Here is the original code that exports the Proposal; no problems with this piece.    
        
        Application.ScreenUpdating = False
        ActiveSheet.Select
        ActiveSheet.Copy
        Cells.Select
        Selection.Copy
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("H:K").Select
        Selection.EntireColumn.Hidden = True
        Range("A1:F52").Select
        Application.CutCopyMode = False
        ActiveSheet.PageSetup.PrintArea = "$A$1:$F$52"
        Range("A1").Select
    
    ' Here's where it gets sticky.  I know I need to somehow reference the Formula Data tab and the specific cell, but I'm not sure if this is correct ...
    
    ActiveSheet.Name = "Formula Data"
    Range("$N$4").Select
    Service = ActiveCell.Cells
    
    'And here's where I'm trying to unhide the appropriate Material tab, depending upon what's in cell N4 on the Formula Data tab (I haven't even attempted to export the Material tab info until I can at least get this command right):
    
    If (Service = "Internet Dedicated Access T1" Or "Private Line T1") Then
    Sheets("1xT1 BOM").Visible = True
    
    ElseIf (Service = "Internet Dedicated Access 2xT1" Or "Internet Dedicated Access 2xT1") Then
    Sheets("2xT1 BOM").Visible = True
    
    Else
    'Not sure how to close out this statement?
    
    End If
    End Sub

    So, that is my conundrum. If anyone can provide assistance or feedback, I would greatly appreciate it. Thank you!

    Last edited by Dirty Laundry; 01-17-2009 at 02:33 PM. Reason: Added code tags, per moderator's request.

Thread Information

Users Browsing this Thread

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

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