Referencing Selected Tabs To Print Selection & Update Table Of Contents Sheet
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:
1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case
2. the ToC has to change depending on the sheets selected.
I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.
What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Referencing Sheet Tabs
Is there a way to use the "value" of a name in a drop-down list to reference a sheet tab name in a formula? A1 has a drop down list. When a name (Bob's Sales) is selected from the drop down list, B1 shows the value of cell D5 from sheet "Bob's Sales".
View Replies!
View Related
Table Of Contents That Will Include Chart Tabs
As I mentioned in my other thread, I have a spreadsheet with 9 worksheets and 200+ charts in individual tabs. As you can imagine, searching back and forth across 200+ tabs is a bear. While I have used Tab Colors to help, it's still way too annoying. Sadly, each chart must be in its own tab in order to ensure that the links to PowerPoint and Word continue to work. Unless I'm missing something, individual tabs are the only thing that have worked for me. I recently found a macro that creates a TOC for worksheets, but it didn't include chart tabs. Is there such a thing, or is there a way to creat such a TOC manually?
View Replies!
View Related
Formula Referencing Multiple Tabs
I'm a bit over my head on this one. I want a formula that does the following: Look at the date I put in on the last tab and find the correct date on the other tabs. Using that date as the column I want it to return the correct row for the data.reference. I am using the HLOOKUP function. I'm not even sure this is the right function. Ont the workbook attached I'm trying to get the data on the Totals tab to come from the Sept Wk 1 through Sept Wk 5 tabs. The formula I tried to use is on the Totals page C7.
View Replies!
View Related
Print Selected Worksheets As One Print Job
I have a macro to print selected worksheets. It works well with one small problem. Whether I select the last worksheet or not, it always prints. I have tried a bunch of different ways to get around this, and all I succeeded in doing was getting a separate page printed first, then it would print the rest of my selection. Sub PrintReportSelection() Sheets("Actual").Select Application.Goto Reference:="Actualprint" ActiveSheet.PageSetup.PrintArea = "Actualprint" Sheets("Deposits").Select Application.Goto Reference:="Depositsprint" ActiveSheet.PageSetup.PrintArea = "Depositsprint" Sheets("Debt").Select Application.Goto Reference:="Debtprint" ActiveSheet.PageSetup.PrintArea = "Debtprint" If Range("SelectVariance") = True Then Sheets("Variance").Select.........................
View Replies!
View Related
Referencing A Cell's Contents As Range()
I'm trying to figure out the best way to reference a cell's contents as a range in VBA code. So essentially it would be simple like this (except of course this doesn't work)... Range("cell(contents,A1)").Select I always look in my CD of old forum posts, Mr. Excel articles, Mr. Excel books I have etc. before I break down and ask.
View Replies!
View Related
Referencing User Selected Workbooks
I would like the user to select two files, the macro then inputs a formula that includes vlookup to the other sheet. However i am not sure how to reference each workbook. Sub InsertLocationContents() Dim rng As Range Dim LastRow As Long 'OPEN CSV FILE WITH LOCATION CONTENTS csvFN = Application. GetOpenFilename(Title:="Select Location Contents csv file") If csvFN = False Then ' They pressed Cancel MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=csvFN Workbooks.OpenText Filename:= _ csvFN, Origin:=437 _ .............................
View Replies!
View Related
Rename Tabs (Sheets) Based On Cell Contents
I have 5 sheets. In the first sheet I have set up 4 cells where I want the contents of them to automatically rename the other 4 corresponing sheets. Eg. In Sheet 1, Cell B6, I want the contents (which will be text) to be the name of sheet 2 automatically. In Sheet 1, Cell B7, I want the contents (which will be text) to be the name of sheet 3 automatically.
View Replies!
View Related
Merge The Contents Of Selected Cells
this is my first post in this great forum. let me start talking about what I need, I used to convert some statement from PDF to XLS format for my work purpose. sometimes I am facing a problem that some text in the statement will come in more than one row, actually I need it to come in one cell but it is coming in several rows that mean, if I want to sort or filter my data, I will lose some important data. What do I want??? I want a macro to merge the contents of selected cells and put it in the first cell of the selected cells then clear the other cells. I will use this macro for the following cases: Case 1- assume we have this data in any sheet. B1=How C1=are D1=you? ( all without any space ) after selecting the said cells and applying this macro, the result will be: B1=How are you? ( one space only between the content of the selected cells ) C1=D1=empty Case 2- assume we have this data in any sheet. B1=How.........
View Replies!
View Related
How Do You Print Only Certain Tabs Based On A Fields Value
I was recently posed with the following by our PMO: "Can I, or how do I, print only those tabs that belong to specific PM? I know I can go and select just those tabs collectively and print but I hate sifting through these 40+ tabs to find the ones that belong to PM "X". Can't you just use, or create, a macro or something?" I replied that this might be limitation of Excel but that I would research it and see. The project template cell reference is D5, there are 11 tabs containing project info for this PM (out of 43), it is running on XP, and is in Excel 2002.
View Replies!
View Related
Clear And Print Tabs In A Worksheet With Checkboxes
I have a workbook with 100 tabs and I have an index page with checkboxes for each tab. I need a macro that will clear all of the checkboxes on the page with one button and a separate macro that will print only the checked tabs. Can anyone write that code for me so I can just add it into my index tab?
View Replies!
View Related
Print Listbox Contents
I have a listbox on my userform that displays records from my worksheet on search. I am trying to figure out how can I : 1) Print the selected Record (line) 2) All Rescords/Lines displayed in the listbox My listbox displays 10 columns (A:J) , but I want to print 11 columns (A:K) in the actual printout. I am thinking of doing this may be by having 2 buttons : "Print All" or "Print this Complaint" or may be using a checkbox to select either option...
View Replies!
View Related
Clear Contents Based On A Selection In Another Cell.
If I have a pick list, or drop-down list, in cell B21 and one of the options in that list is "Clear", how can I get the contents of cell C21 to be cleared when I select the "Clear" option in B21? Constraint... Don't want to use VBA. I am thinking of hiding a formula somewhere other than in C21 that evaluates B21 and does an If Then kind of deal to clear C21.
View Replies!
View Related
Print Based On Cell Contents
I have put together a spreadsheet to help with the creation of work orders. I have a summary page that the user can define which category the work order falls under along with this I have a couple of macros set up that will print a specific work order pertaining to the category they selected. I have run in to a few problems (mostly training users) that I would like to eliminate up front. I am looking for a macro that I can use to look at the contents of a cell (contains a word that corresponds directly to the name of one of the sheets in the work book) and print the worksheet that has the same name as the contents in the referenced cell.
View Replies!
View Related
Filter Cell Contents To Print
I have an excel worksheet that gets populated from VB6 and Access 2003. When the program runs, populates the database, any empty fields are set to = "N/A" or 0 (number zero). When the information is passed to excel, one cell could have several records. For example, the database may have "Area1 Area2 N/A 0" that gets passed to the worksheet cell. What I would like to do is to have excel ignore the N/A and 0. Is it possible?
View Replies!
View Related
VBA - Userform: Click In A Text Box, Contents Is Selected
I'm building a userform (for the first time), and I'm working with a textbox control. I'd like to make it so that when the user clicks in that textbox, the contents is selected, so that if they begin typing, it will type over what is already there. My question, specifically, is whether or not there is a property I can set to make this the default behavior, and if not - what event do I tie to this action? Is it the "Enter" event? What I have now, which will probably be unacceptable to my users, is code that looks like this: Private Sub tbName_Enter() ActiveControl.Value = "" End Sub This makes the contents disappear, but I'd rather they stay there and just become "selected".
View Replies!
View Related
Audit The Selection Of Players Selected
I have this macro and I would like to audit the selection of players selected. I would like the number that is entered in the input obx to be placed in the sheet "DRAFT" and be placed in A1 and the next in A2 and so on. Can this code be modified for this to occur. Sub SearchDelete() Dim ID, c As Range ID = InputBox("Enter the Super10 Player ID Number", "Super10 Player Search") If IsNumeric(ID) Then With ActiveSheet.Range("a:a") Set c = .Find(ID, LookIn:=xlValues) If Cells(c.Row, 11).Value = "" Then Msg = "P L A Y E R A L R E A D Y S E L E C T E D !" & vbCr & vbCr & "Player # : " & Cells(c.Row, 1).Value & vbCr & "Name : " & Cells(c.Row, 4).Value & vbCr & "Games : " & Cells(c.Row, 6).Value & vbCr & "Average : " & Cells(c.Row, 7).Value & vbCr & "Position : " & Cells(c.Row, 2).Value & vbCr & "Team : " & Cells(c.Row, 3).Value & vbCr & "Rank : " & Cells(c.Row, 27).Value & vbCr & "Rank POS : " & Cells(c.Row, 28).Value.............................
View Replies!
View Related
Print The Selected Pages
There are 24 pages for printing and each one has different information on that sheet. From day to day some or all of the pages are needed. I would like the macro to look at each page and find a cell(different cell but same info on each page) and if the value of that cell is greater than 0 print that page. If not don't. I only need to calculate the "Bundle Tops" sheet. How do I write this. I am a beginner at the VBA code.
View Replies!
View Related
Compare And Update Selected Data On Two Sheets
Each monday i get a new price list from our supplier. My job is to compare this list with ours and update if neccesary. The reason, why I'm writing here is simple -I need to automate the process. Since their and our pricelist is somewhat different, it's only possible to use selection comparison. So, I need something that can do this: 1. First I open those two files and make a selection on both of them (like all the apples on the supplier list and all the apples on our list) 2. Push a button that executes a code 3. The code compares a value in the first column 4. If it finds a match, compares the data in second column 5. If data is same, color the cell (or the text) lets say yellow 6. If data is different, update field in our pricelist and color the cell (lets say red), so I can find and recheck it later 7. If the supplier has a _new_ product, the code will insert it somewhere in our list. Doesn't matter where, it may as well be a new sheet 8. Compare the next cells in selected area
View Replies!
View Related
Allow Users To Print Hidden Sheet But Not Change Print Settings
I am having trouble disabling the "Preview" button when calling Application.Dialogs(xldialogprint) in XL2003. I have an xlSheetVeryHidden worksheet containing a form which is made visible programatically when the user wishes to print a copy (code below). I want the user to be able to have access to the functionality of the xlDialogPrint dialog (i.e. select desired print destination, number of copies, pages per sheet etc.) but I also want to prevent them modifying the page setup of the protected worksheet by initiating a preview from the print dialog. Private Sub btnPrintReturnForm_Click() wbkRUSC.Unprotect sysPass 'unprotect workbook to enable changes to sheet visibility ' (sysPass is project constant string containing password) shtStaffForm.Visible = xlSheetVisible 'make staff form visible shtCalc.Visible = xlSheetVeryHidden 'hide main calculator in case user gets smart and hits..............................
View Replies!
View Related
Macro To Print Selection
I seem to be getting myself into a complete muddle as was wondering if someone to help me with a Printing problem. On the attached sample, i'm trying to write a macro to print the selection but only print where the status is "Active" to the ned of the selection. However, the spreadsheet is changing on a daily basis i.e. new lines being added and lines being taken off. So, to take potential of user error out i wanted a macro to print the "active" selection.
View Replies!
View Related
Update Cells Based On A Dropdown Selection
I am trying to use a row of cells as update cells, where the user inputs an amount into greyed out cells, which in turn updates Sheet1!$I$2:$I$11, from which it will update other worksheets that are currently in progress. but I do not know how to go about it. Is it possible? For each amount the user enters (for each Code Number) a date will be displayed below the amount.
View Replies!
View Related
Auto Referencing The Date To A Table
On XL2003 i am putting together a worksheet that has a list of tables by month i.e. a table for January then a table for February below it and so on... Within each table the data is referenced as follows: =SUM('V:Product Testing InformationAuto final test stationsTest ResultsSAP Files[Test Data070801.xls]Sheet1'!$AV$18) How can I change the highlighted date to reference a cell containing the date? i.e. =SUM('V:Product Testing InformationAuto final test stationsTest ResultsSAP Files[Test DataA2.xls]Sheet1'!$AV$18) In cell A2 would be "070801"
View Replies!
View Related
Set Print Area From Selected Range
I have some code which selected a certain amount of columns depending on whether they contain values or not. Once I have this range set and I select it I want to set this as the print area. I am not quite sure how to do this. for example I tried: rngUnion.Select ActiveSheet.PageSetup.PrintArea = ActiveCell. CurrentRegion.Address
View Replies!
View Related
Update Certain Rows Based On The Choice Selected From A Dropdown List
Is it possible to update certain rows only based on the choice selected from a dropdown list I shall explain this scenario I have attached a small excel sheet called TESTTHEM which i'm testing and learning excel simultaneously. On the attached sheet If i select a value in B4 as BusinessLogic, it should then lookup for relevent rows in MISC worksheet and copy C5:L5 from MISC worksheet and replace the columns G4:P4 Is it possible to do this in excel?
View Replies!
View Related
Print Selected & Multiple Rows To A Form
The file consists of 3 sheets:Orders,Order Form PO1 & Purchase Order. When I mark "x" in the row that I want to print, Order Form PO1 will be displayed with all data in the specific columns. However, it only allows me to select one row at a time. Now, I want to select multiple rows so that it will pass the data to the sheet Purchase Order.
View Replies!
View Related
Print To PDF Selected Sheets From An UserForm Listbox
I have created a custom Menu (excel add-in) to make my work easy in excel. My problem is to print only selected sheets from Workbook in one PDF file, for that I've created a Userform with 2 listboxes, add sheet and print buttons. In the first listbox are listed all the sheets and in the second listbox are the sellected sheets to be printed. What I've succeded so far is to print selected sheets, but it creates one PDF file for each sheet, only if I put my code in workbook and not in Menu add-in (.xla file). As PDF Printer I use PDF reDirect Pro v2.
View Replies!
View Related
Macro To Select An Area, And Print Selection
I need a macro to select the area within the blue invoice sheet box, so everything within the blue invoice sheet box is selected , and then the selection should be printed. I will have hundreds of these invoice slips made, right below each other, i was wondering once this is done, is there an easier way to to have each invoice selected, instead of making a custom button for each sheet ( which selects just that invoice , and prints that selection ). ( Column m through v, starting at row 2 stoping at row 68, is the selection required in this example, everythign within the blue box. ).
View Replies!
View Related
Date, Cell Selection And Print Macro
There's a macro I'm trying to figure out for a calendar I'm working on. Here's what it has to do. 1.) Selects the cells for the current month (I assume the user would have to click on the current month itself) plus the two upcoming months. i.e. November, plus December and January. 2.) Sets the selected cells as the area to print, then prints those cells. If there's anything that might be a challenge, the numbers for the days are all text boxes, if that makes a difference. I've been experimenting with different formulas with no luck.
View Replies!
View Related
Automatically Update A Selection In A Drop Down List From A Cell Value Change
I have two worksheets... Sheet 1 A1: description A2: Target Iteration A3: Concat A1, A2. Sheet 2. A1: Drop down datavalidation list selected from A3, sheet 1. On sheet 2, a user can select from the list. If a user changes the value in A2, sheet 1, I want the value that is associated and already selected in A1, sheet 2 to automatically update. There is a 1 to many relationship with the concat and the drop down. In that, sheet 2 can have multiple rows with the same value from sheet 1 A3. Is there a way when A3 sheet 1 changes, to search in A:A in sheet two and update the values for those records that match the original value in A3, sheet 1?
View Replies!
View Related
Update Print Header And Footer With VBA Code
I am trying to link what is in the header and footer with information contained in certain Cells. (I eventually want to have a user form to help input the information, but one thing at a time.) How do I go about getting the following information into code so that I can update the enclosed code? Left Header: Client Name contained in Sheet: "Table of Contents" B:1
View Replies!
View Related
View / Print Selected Options Contiguously Separate Page
I’ve created a worksheet containing many drop down lists that form an inventory of household effects. Once selected, I’d like to be able to view/print the selected options and quantities separately on one A4 page. I don’t want the separately viewed/ printed page to contain anything other than the selected options. The problem I’m having is to arrange this new page as a contiguous list.
View Replies!
View Related
Print Selected Sheets 1004 Application Defined Or Object
i have found this code on the net but can only get the control sheet to work when i run macro "PrintSelectedSheets" i get a run time error 1004 application defined or object error i have put the code in the workbook object but having problems, it seems to be because the sheets that i am trying to print are hidden can this code be edited Sub PrintSelectedSheets() Dim i As Integer i = 2 Do Until Sheets("Control Sheet"). Cells(i, 1).Value = "" If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select 'ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End If i = i + 1 Loop End Sub This code seems to work ok Sub CreateControlSheet() Dim i As Integer On Error Resume Next 'Delete this sheet if it already exists Sheets("Control Sheet").Delete On Error Goto 0...........................................................
View Replies!
View Related
Pivot Table Combining Several Tabs
Good Afternoon. I am attempting to create a pivot table using 4 columns from 5 different tabs. I have created pivot tables before using one tab and they are pretty easy. However, I have been working on this for over an hour and I am getting nowhere. Can someone tell me how to put all five tabs into one pivot table? The columns are all in the same order on each tab. They are as follows: System, Management Area, Schedule Date, WO Type. I want the pivot table to have System and Management area on the left side, date on the top row and counting the number of WO Types.
View Replies!
View Related
Auto Duplicate Row Contents From One Sheet To 2nd Sheet If Column Contains Set Words
I want to end up with is when I type text in any row of the 'Master' sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (referred to by text entered in column O). For example row 5's contents from the Master sheet would duplicate into the sheet named 'Accommodation' as O5 contains the word Accommodation. In addition, should the details ever change in the Master, this would also need to be reflected in the individual sheets. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie if row 5 had the first occurrence of the word Accommodation in column O, the contents of that row would be placed into the next available first blank row of the Accommodation sheet, which in this case would be row 2 as the headings are on row 1.
View Replies!
View Related
Auto Duplicate Row Contents From 1 Sheet To 2nd Sheet If Column Contains Set Words
I want to end up with is when I type text in any row of the Master sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (as per column O). For example row 5's contents from the Master sheet would duplicate into the sheet named Accommodation. In addition, should the details ever change in the Master, this would need to be reflected in the individual sheets also. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie row 5's contents to be placed into row 2 of the Accommodation sheet seeing as it's the first occurrence, and row 6's would go into row 3 etc.
View Replies!
View Related
Table Of Contents With Statistics
I have a table of contents page. The TOC is updated each time some one opens the sheet (this is a must have based on the requirements). I'm trying to include some statistics next to each item in the TOC. These statistics are on each worksheet and just need to be copied to the TOC. Now my problem. The worksheets are NOT keep in alphabetical order, so After the TOC is created a sort is done to put the TOC links in alphabetical order. After the order is set, I need a formula that will read the worksheet name from a cell on the TOC (which is really a hyper link to a worksheet). I have this already: ActiveCell.FormulaR1C1 = "=Address!R[-2]C[-2]" If "=Address!" (Where address is the name of one of the sheets) could be replaced with a cell reference that (I think) would do the trick. My TOC is a MACRO not a cell formula so if this can not be done with a cell formula but can be done through VBA that would be fine.
View Replies!
View Related
Table Of Contents With Hyperlinks
I am attempting to build a table of contents and add hyperlink to each entry as suggested by dynamic sheet index The TOC is constructed properly, but when I click on the hyperlink I receive an error message: " Reference is not valid" My code (courtesy Jabax): Sub Create_TOC() Dim wb As Workbook Dim wsTOC As Worksheet Dim ws As Worksheet Dim r As Long Set wb = ThisWorkbook Set wsTOC = wb.Worksheets("TOC") ' Turn the next two lines of code on and off as preferred ' Ans = MsgBox("Do you want to update the Index?", 4) ' If Ans = vbNo Then Exit Sub With Application . ScreenUpdating = False .Calculation = xlCalculationManual .DisplayAlerts = False End With...
View Replies!
View Related
VBA - Creating Separate Files And Tabs From A Table
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out. I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook. I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
View Replies!
View Related
|
TRACKER
