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!
Bookmarks