Pivot Tables: Alternative to PivotSelect?

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Create XML 'baseName' or 'nodeName' Hi, Trying to use VBA to create an XML file... I've got half of it figured, however I don't know how to create a node... I've tried: docXML.ChildNodes(0).baseName = "name" but it won't work because 'baseName' (or nodeName) is a read only element... Any ideas...??? Also, can you tell me how to refer to nodes by their names, rather than ChildNodes(0, 1, 2, 3, etc.) - I can't figure out '.attribute' (which should be it...??) Thanks...! Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014973
  - 2
    - Worksheets Macro I am trying to create a code that, when I run it from Sheet 1, it will 1. Clear the contents in cells C10 and E10 on Sheet 2 2. Copy the contents in cells C7 and E7 on Sheet1 3. Paste the contents from cells C7 and E7 on Sheet 1 into C10 and E10 on Sheet 2 4. Finish with Sheet 2 as the active worksheet. Can you help me out with this? Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014966
  - 3
    - Excel Addin File Size Any thoughts on control Addin file size? In the past, i could reduce the file size of an addin, by exporting/removing every module/form, then re-importing these files, and saving. I think this would also possibly get rid of the compiled component. This process does not seem to work with Excel 2007. My particular situation is I added about 100 lines of code to a program that previously had easily 5-10,000l lines of code that was stored in about 0.5 MB, and now my file has ballooned up to 4 MB, 8x bigger. Thanks. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014960
  - 4
    - Recognizing Ctl or shift key in VBA Does anyone know a way of recognizing whether the shift key or control key are pressed from inside VBA. I want a userform button to behave differently if one of these keys are pressed. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014959
  - 5
    - Uncheck child nodes if parent node is unchecked in treeview I'm trying to use a Treeview control. I'd like to have all child nodes be automatically unchecked if a if I uncheck a parent node. Also, is there a way to specify that if a node contains child nodes it'll automatically be bold? I've got drag 'n drop working on my treeview and so I'd like any parent nodes to bold themselves. I would love it if someone could help me with this. Even better; does anyone know a really, really good and simple to understand novice to advanced tutorial on how to program treeview controls? This would help prevent me from bugging everyone about my treeview woes. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014955
  - 6
    - comparing lists I want to compare 2 lists and extract the differences to a third, and indicate why they are different. I have a list of 300+ current employees, but current as of a few months ago. Every few months HR sends a current list as of right now. Is it possible to populate a third sheet by comparing the 2 lists, and having employees on the new list only marked as a new hire, and those on the old list only marked as terminated? I can do it on each sheet separately, but I am trying to be elegant. Thanks, Richard Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014943
  - 7
    - Determining dates based on colored cells. I'm using Excel 2000. What I'm trying to do is as follows: Lets say I have a row of numbers that are fiscal weeks like this, 1 2 3 4 5 6 7 8 9 10 and i have a row of empty boxes underneath them, which when changed to the color green, i want seperate cells to give the start and end dates of those fiscal weeks. How would I link the fiscal numbers to dates? and what kind of if-then statement would I need for it to calculate a time span? for example: green boxes under fiscal weeks 5 to 7 1 2 3 4 5 6 7 8 9 10 [g][g][g] then have two seperate cells, containing the calculated Start date, and the other with end date. i'd imagine linking the fiscal numbers to real dates would allow excel to add and subtract them. if it's too complex to be answered i understand, thanks ej Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014934
  - 8
    - using formuals I have a repeating IF statement running down column A for 200 rows. I'm making an assumption that I will not need more than 200 rows ever but I have no idea how many rows I may need. My issue is thta because of the 200 rows being used by the formula when I print the worksheet it prints all 200 rows now just the rows that have real data. If there a way besides having the user set the print area to only print rows with real data?? Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014931
  - 9
    - Changing up to 2 of 3 vals and the 3rd val is automatically Calcul Hello; Below is a brief description of the problem and the code: There's a starting value in Each of the UNLOCKED cells C12, C14, C16. The purpose of the w/s Change event is (3 scenarios for now): ...If the value in cell C12 is changed manually, cell C16 is automatically calculated ...If the value in cell C14 is changed manually, cell C16 is automatically calculated ...If the value in cell C16 is changed manually, cell C14 is automatically calculated The manually changed values are displayed in dark yellow fill. ==================================== Private Sub Worksheet_Change(ByVal Target As Range) Const sInputCells = "C12,C14,C16" ' manually input ANY 2 of C12, C14, C16 values and the 3rd is automatically calculated ' and displayed in light green fill With Target If Not Intersect(.Cells, Range(sInputCells)) Is Nothing Then If .Column = 3 Then On Error GoTo ErrHandler Application.EnableEvents = False If .Row = 12 Then 'calc C16 .Interior.ColorIndex = 6 .Offset(2, 0).Interior.ColorIndex = 6 .Offset(4, 0).Interior.ColorIndex = 35 .Offset(4, 0).Formula = "=E12*C19*60/C9/C14" ElseIf .Row = 14 Then 'calc C16 .Interior.ColorIndex = 6 .Offset(-2, 0).Interior.ColorIndex = 6 .Offset(2, 0).Interior.ColorIndex = 35 .Offset(2, 0).Formula = "=E12*C19*60/C9/C14" ElseIf .Row = 16 Then 'calc C14 .Interior.ColorIndex = 6 .Offset(-4, 0).Interior.ColorIndex = 6 .Offset(-2, 0).Interior.ColorIndex = 35 .Offset(-2, 0).Formula = "=E12*C19*60/C9/C16" End If End If End If End With ErrHandler: Application.EnableEvents = True End Sub ==================================== With unprotected sheet and only the above w/s Change event in effect ( XL 2003 Options: 1,000 Iterations, Max. change 0.00001, though not really needed!): 1) procedure works fine by manually changing the values in C14 and/or C16 2) procedure works fine by manually changing the value in C12 provided the preceding manual change(s) was in C14 3) procedure FAILS when manually changing the value in C12 if the preceding manual change(s) was in C16. So it seems to me that the problem (circular ref., 0.00 or DIV/0!) is most certainly with the above Change event code. I would be glad to attach the simple test w/b (single w/s), if I only know how !! Your help in identifying the problem would be greatly appreciated. Thank you kindly. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014921
  - 10
    - hot get a cell value in varable excel does not support more than seven nested if statements and i have around 20 unique values on which bases i have to fill other filed . for this i want to use case statement in visual basic but unable to get the cell value in a variable if any other option Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014919
  - 11
    - How to copy a range of cells from one sheet to another I want to copy a set of cells, the values, from one sheet to another in VBA. From "RawData" to "Compare" where x is a row, y is a row and z is a column. These will be changing as the code executes. I have tried this: Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) Compiles, but gives a run time error. So then I tried this: Set rng1 = Sheets("Compare").Range(Cells(x, 1), Cells(x, z)) Set rng2 = Sheets("RawData").Range(Cells(y, 1), Cells(y, z)) rng1.Value = rng2.Value Compiles, but I get a run time error at the first Set. Sure, I can create do loops to copy cell by cell, but surely there is a better way. Why do these methods not work? What is a good method to use? Thx Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014915
  - 12
    - IF Statement I am working with a document that shows pay rate % change for each year for each employee. If they show any number (positive or negative), it means they have had their yearly review. How can I create a total row that shows how many people this past year still need their reviews? I was thinking of using an if statement but I am unsure of how to set it up.Thanks Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014904
  - 13
    - returning a row number Hi, I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014900
  - 14
    - Bar Graph in Excel 2007 I am using Excel 2007 and am working with a bar graph. I have actual data from Jan. â??06 though Sep â??08. I would like to modify the graph to automatically pick up new months when I add data. Now, each month I edit the data formula to pick up the new month. I tried extending my data range from Oct â??08 to Dec â??09, even though there is no data yet. The graph showed the 15 months without data which is not what I want. Is there a way to make the graph only display the bar if there is data in the data range (not blank)? Thanks, John M Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014893
  - 15
    - Macro Security I have a excel worksheet that has many macros and the worksheet is quite large. I'm not sure what happened, but all of a sudden the worksheet opens and will not run any macros. It has the security level is high. I check the security level and it's on medium. the worksheet used to ask the "enable" or "disable" macro question when opening the worksheet, but all of a sudden it doesn't. And like I said, the security level is medium, not High. When I open my worksheet I can't run the macros!! Help!!! What can I do to force the enable/disable macro dialog box to come up on opening the worksheet? Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014892
  - 16
    - Macros I wrote the following macro and assigned it to a button a few years ago, I don't know under which version of Excel. It still works. I created it by entering commands manualy and having it recorded as a macro. =========== Sub Button9_Click() Application.Goto Reference:="e" Selection.EntireRow.Insert SendKeys "{UP}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}", True SendKeys "^{c}{DOWN}{ENTER}", True Application.Goto Reference:="e" SendKeys "{DOWN}{RIGHT}{RIGHT}", True ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C[5]" Application.Goto Reference:="e" SendKeys "{UP}", True End Sub =========== 1) I can run the macro under XP. 2) I cannot run it under Vista. As soon as it hits the first SendKey, it complains about some security breach. 3) I can not rerecird it from scratch under any new Excel (Vista or XP). It doesn't generate SendKeys. It just moves to the final cell. Which is useless because I'd like to get there dynamiccaly based on the orig cell. Any help will be appriciated. EB Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014886
  - 17
    - How to make a VLOOK UP OR HLOOK UP formula I have 7 columns and 3 thousand rows, 6 of the columns will be field with numbers. I need to organize the numbers in the cell by rows, not by columns in ascending order. In addition, I need to be able to locate specific numbers in the rows: For Example: find #38 in all the rows and then mark the 7th column next to to the row with a #1 the row that don't have #38 live in blank. THX. in advance Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014885
  - 18
    - Function to Check if Cell is Highlighted for Change in Shared Work Good afternoon, everyone. I've been banging my head against the wall trying to figure this out for a few days. I am looking to create a shared workbook and utilize the track changes feature. In this workbook I want to write a function that refers to another cell and tells me whether or not that cell is highlighted as being changed (i.e. has the blue triangle in the top left corner). It is extremely important that it refers to whether or not Excel is highlighting it because I want the function result to change if the highlight change date range is changed. I figured out how to have the function check a cell for comments, but since these only look like comment it doesn't work. I'm hoping the VBA language is similar, but for the life of me I can't find how to reference the highlight for change. Any help would be greatly appreciated! Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014876
  - 19
    - How do I set auto-sort for a column in a worksheet? Hi; I need to create a worksheet to keep record of customers. I have the following example: NAME PHONE ADDRESS CONTRACT WK1 WK2 Phil 123-456 Angel 321-654 John 987-630 I need to keep the first row as the titles row, and need the worksheet to automatically sort in alphabetical order based on the name, and to do it everytime a new customer is added to the list. HOW CAN I DO THAT? Regards, FVO Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014875
  - 20
    - Show checkbox checked on open I need help. I have 3 userforms. Userform1 has 2 buttons, one to start a new project and one that edits the project as saved. Which ever button is clicked brings up userform2 which has 5 checkboxes. What I would like to happen: when the edit button is clicked I would like the checkboxes that were checked the last time to show up as checked. The value of the checkboxes are stored on sheet3 in cells c2-c6. Thank you for your time and assistance. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014846
  - 21
    - ClickMe I've drawn a button - ClickMe on a work sheet and written a little code for it. [I am used to vb code in the Access environment] NOW!! how do I make the button user clickable please? -- Jim Bunton Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014842
  - 22
    - Application.VLookup I'm trying to programmatically insert comments into columns on multiple worksheets in a workbook. Problem is that it works on 2 of the 3 sheets and not the 3rd. There's a VLookup that matches the data in one column on each sheet by looking for a number. But on the 3rd sheet it craps out. I'm desperate for an answer right now...can anyone help? sPayer = CStr(Application.VLookup(Range("C" & iRow).Value, Range("Payer!C:ZZ"), i - 2, False)) Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014840
  - 23
    - Excel 2007 and Named Ranges I encountered a problem in 2007 which I believe is a bug. Just wondering if this is a known issue or not. When we programatically delete a row using rows(X).entirerow.delete we noticed that in 2007, that a named range that was a row beneath the deleted row did not shift up. In 2003 and with insert in both editions and going through the Excel UI, named ranges shifted when rows were inserted/deleted, just programatically deleting rows/columns did not shift the named ranges. Am I missing something or is this a bug? -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 User of MS Office 2003 Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014836
  - 24
    - Ribbon Button not Working with my Referenced Workbook I have a workbook named "Global Schedule" that my users open with Excel versions 2003 and 2007. I have a 2003 Add-In that I reference which contains all of the code to manipulate the Global Schedule. If 2003 opens the Global Schedule I have code to add a custom popup menu with some control buttons. These buttons do custom sorts to the Global Schedule. This part works beautifully. If 2007 is used to open the Global Schedule then a 2007 Add-In is only used to change the Ribbon which also contains custom buttons to do my custom sorts. I want these buttons to call the same procedures that are located in the referenced workbook - 2003 Add-In. Can I not call procedures from a referenced workbook in XML? The problem happens when I click the Ribbon control butttons. I get an Error: "Wrong Number of Arguements or Invalid Property Assignment." I checked the XML code in the CustomUI Editor and the buttons onAction has the correct procedure pointing to the 2003 Add-In reference. I'm not sure what this means because my procedures that the control button calls does not require arguements. Does anyone have any ideas? Here is my basic setup: 'GLOBAL SCHEDULE CODE: Private Sub Workbook_Open() If Val(Application.Version) > 11 Then ' open 2007 Add-In which contains XML to change Ribbon Else Call CustomMenuControl(ThisWorkbook) End If End Sub The 2003 Add-In is always open no matter which version of Excel is used because it contains several modules that I reference to manipulate the Global Schedule Data. -- Cheers, Ryan Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014834
  - 25
    - VLOOKUP FUNCTION Is there a way to return the next largest value using a vlookup function when an exact match is not found? As of now, it keeps returning the value that matches or the next smallest value. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014831
- Next
  - 1
    - Excel 97 I just got thrown a random assignment at work of making Excel spreadsheet made in 97 work with Excel 2007. I have ZERO experience with any form of VB (I have a C background). First, is there anyway make Excel 2007 backwards compatible with Excel 97 code. This, of course, would be the simplest solution. Second, if I have to update the code, how should I approach this with no VB experience? Should this require drastic changes, a complete rewrite, or only minor fixes? I know this can vary case-by-case, but I am looking for generalities right now. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014827
  - 2
    - Copy Formula I have a formula in a cell. The formula is: =150+200 I wish to copy that formula and place it in another workbook as =150+200 and not the resulting value 300. How do I do this? Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014824
  - 3
    - Should an automated version of Excel close? I'm writing an application in C++ which will read a couple of complicated text files and create an Excel worksheet containing data extracted from these files. In the process of testing, I got to the point where the program: - used CoCreateInstance() to create an instance of Excel and obtain an IUnknown interface - used QueryInterface() to obtain an IDispatch interface - used Invoke() to set the Visible property to TRUE At this point I can see an instance of Excel with no workbook visible on the computer. If I let this test version of the program go ahead and call Release() on each of the interfaces which it has obtained, the instance of Excel closes. This I take to be normal behavior of an automated application. After adding code to get an IDispatch interface to the Workbooks object and calling its Add method, the instance of Excel has a workbook containing three worksheets, just as if it had been started by clicking the Excel button. Now, however if the program proceeds to call Release() on each of the interfaces, Excel does not close. I have to manually close it by clicking its close button. Is this normal behavior? I'm concerned that I may have failed to release an interface although I can't see what that interface might be. I know that I have a IUnknown and IDispatch interfaces to the original Excel object, an IDispatch interface to the Workbooks member, and an IDispatch interface to the Workbook returned by the Add method. The program calls Release() for each of these. Is there anything else I should look for? -- Norm To reply, change domain to an adult feline. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014822
  - 4
    - sum in range meeting criteria hi, i have a value in cell A10. i want to sum numbers in range B1:D8 where the value in A10 value is equal to a value in range A1:A9. how can this be done? thanks in advance, geebee Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014815
  - 5
    - Selective Ribbon miminise Hi... Is it possible to for a macro to disable the ribbon but keep the quick access toolbar off which I want to use my own toolbar? Cheers Gordon... Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014813
  - 6
    - Can VB execute VBA code? My VBA macro runs slowly in Excel 2007. Will Visual Basic compile my VBA macro into a faster application that I can somehow use with my Excel spreadsheet? -- TomThumb Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014811
  - 7
    - lookup returning #N/A i have the formula, =IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densities!O:O,0),2)) This returns #N/A if the F21 string can not be found It there anyway to get it to return 0 or empty insstead? Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014808
  - 8
    - Excel 2007 Adding Template Sheets and Invalid Name Error (#NAME?) I have a workbook (.xlsm) that gets built by adding in template sheets (.xltx). There are numerous template sheets that can be added depending on the needs of the workbook. Each template sheet refers to values found on the the Master sheet by named ranges. The values on the Master sheet are unique by workbook, therefore the template sheets cannot be linked to the Master template sheet. The formulas on the template sheets use workbook level named ranges to refer to the values on the Master sheet. The ranges on the Master sheet are defined as workbook level named ranges. When I do a Sheet.Add Type:=<path> the template sheet is added to the workbook, however, all the named ranges that refer to the Master sheet show an Invalid Name Error (#NAME?). If I click on a cell with an Invalid Name error and then click in the formula bar and then click on any cell in the workbook, Excel resolves the invalid name. Nothing is changed, just 3 mouse clicks. Any idea why Excel can't resolve the names when the sheet is added? Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014801
  - 9
    - My Workbook is always hidden when I open it? I don't know what I've done to it. I have on open and before save events, but I can't see the problem. I want the document to open visible. Can you have a look and tell me if there's something I'm missing? Private Sub Workbook_Open() Dim wbk1 As Workbook Dim wbk2 As Workbook On Error Resume Next Set wbk1 = Workbooks("Export_Requests.csv") Set wbk2 = Workbooks("Project Summary.xls") If wbk1 Is Nothing Then ChDir "I:\A&T Contracts\000 Utilities\" Set wbk1 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Export_Requests.csv") ActiveWindow.Visible = False End If If wbk2 Is Nothing Then ChDir "I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Option E\Payment\" Set wbk4 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project Specific\Contract Docs\Integrated Utility Services\Option E\Payment\Project Summary.xls") ActiveWindow.Visible = False End If End Sub '******* Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim i As Integer Dim sPath As String Dim sFilename As String Application.ScreenUpdating = False Range("A1").Select 'Backup to Flashdrive msg = "Do you want to save a Backup to your Flashdrive?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then On Error Resume Next Set fs = CreateObject("Scripting.FileSystemObject") For i = 67 To 90 Set drspec = fs.GetDrive(Chr(i)) If drspec.drivetype = 1 Then 'MsgBox "Drive " & Chr(i) & " is the removable drive" sPath = Chr(i) & ":\Work\Solutions Folder\IUS MONITOR\" sFilename = "IUS Monitor " & _ Format(DateSerial(Year(Date), Month(Date), _ Day(Date)), "dd MM yy") & ".xls" ans = MsgBox("Save File as " & sFilename & "?") If ans = vbOK Then 'Check Path exists If CreateObject("Scripting.FileSystemobject") _ .folderexists(sPath) = False Then MkDir sPath End If ActiveWorkbook.SaveCopyAs sPath & sFilename End If i = 90 End If Next i Else ActiveWorkbook.Activate End If Application.ScreenUpdating = True End Sub 'Thanks 'Dylan Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014785
  - 10
    - lookup two columns I have two long tables with nanes and numbers next to them Is there a way to look up both tables in the same formula The names colums are C and G The numbers columns are D and H The tables are on a seperate worksheet Densities Looking up what is in cell F21 Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014783
  - 11
    - Disable selective events In the code below I use Application.EnableEvents = False to prevent the showing of UserForm1 while the code is running. The problem is that it prevents all events from happening. Is there any way to prevent UserForm1 from showing while still allowing other events to happen? Private Sub CommandButton1_Click() Unload End_Of_Month_Disposal Application.EnableEvents = False Application.ScreenUpdating = False ActiveWorkbook.Worksheets("2009 Training").Sort.SortFields.Clear ActiveWorkbook.Worksheets("2009 Training").Sort.SortFields.Add Key:=Range( _ "A3:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("2009 Training").Sort .SetRange Range("A2:N5000") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B2").Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014772
  - 12
    - Lookup not working I am using this macro but I have two problems when it runs 1 - If the text being searched for isnt there the it still returns a value which is wrong 2- If the text being searched for is there but the result fiel is empty it returns a zero i need it to leave the cell empty Thank you "=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))" Range("H21").Select Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault Range("H21:H39").Select Range("H21").Select End Sub Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014770
  - 13
    - lookup returns zero or the wrong number "=IF(RC[-2]="""","""",IF(R[-13]C[-2]=""WBM"",LOOKUP(R[11]C[-2],Densities!C[-5],Densities!C[-4]),LOOKUP(RC[-2],Densities!C[-1],Densities!C)))" Range("H21").Select Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault Range("H21:H39").Select Range("H21").Select End Sub Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014769
  - 14
    - Error in loading DLL If I open a spreadsheet with a macro and double click on a block and type something then press enter I get the following error Microsoft visual basic error in loading dll. I did check under tools prefrences but none of the library's seems missing. If however I uncheck microsoft office 10.0 object library the spreadsheet works. It only does this on one notebook. Reinstalling office didn't resolve the issue. Any ideas please. Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014767
  - 15
    - Unable to populate a range with a formula Greetings! This one has me stumped. I am trying to populate a range with a formula. The code is: Sub TestSetAC2() Range("AC2:AC10").Formula = "=SetAC2" Exit Sub End Sub Function SetAC2() SetAC2 = 5 End Function First I opened a blank worksheet. When I execute the procedure, I get #Name? thruout the range. The tool tip just to the left of #Name? says that the formula contains unrecognized text. Any help will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014756
  - 16
    - Excel vs Lotus [pop-up screen with directories] I'm not sure how to explain my problem. It took some time to convince a friend to take the step to use Excel and convert his Lotus data files. He's about 90% there (unbelievable but true!). He uses a network at his workplace [drive k:\] where the files are stored in various directories [6 major elements] and subdirectories [12 branches and for each year]. His laptop is updatedand, when not connected to the network, he runs a DOS(!!) program which enables him to run the k-drive files on the laptop. A pop-up screen appears with Lotus icons [major directories] if double-clicked another pop-up screen appears with the sub-directories and if selected opens the specific file. He calls this his file organizer and challenged me that its not possible with Excel. I told him that I don't know how, but I know some guys out there [which is you!!] For what it's worth -The DOS file reads: SUBST K:/D SUBST K:C:\ Any form of assistance would be much appreciated. -- HJN Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014733
  - 17
    - Macro question Hi there, I have two column look like this A 1 2 3 4 B 1 2 C 1 2 3 D 1 2 And I want it to be A 1 A 2 A 3 A 4 B 1 B 2 C 1 C 2 C 3 D 1 D 2 Another word to fill the empty space with the above letter It always start with some letter and number at the top Thanks, Oded Dror Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014723
  - 18
    - To change command button caption Dear All I am in sheet2. I click a custom tool bar item, then it has to goto sheet1 and change command button in sheet1 by name 'alfa" caption from "go to ballast" to "go to cargo" . Have been getting error when using activesheet.shapes.("alfa").caption= "go to cargo" Pls suggest right code n thnks brgds/captgnvr Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014717
  - 19
    - ListBox multi select updating in database Hi I am stumped- I have a populated listbox that I need to be able to use to find my selections in a database and enter a defined date into the first empty cell in their row. I honestly have limited VBA experience but anything would help. Basically how do I edit/manipulate the selections from a listbox once I have them selected? Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014711
  - 20
    - ignoring blanks cells while importing data Hi, I have been importing data from another excel sheet. I use ='C:\pc ranch report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46 and dragged the cell to let it automatically copy all the data from the other excel sheet. However, The data from the other excel sheet is automatically generated, so it might have different number of columns. That's why I dragged the cells down a few more rows than the original excel file has to leave extra space just in case there might be more columns generated in the next generation. The extra space that I dragged shows 0 or #REF. Can I add something in front of the ='C:\pc ranch report\[ca_outputs_yearly.csv]ca_outputs_yearly'!A46, so the empty cells won't show up as 0 or # Ref?? Thank you so much!!! Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014706
  - 21
    - How to Select Last Row of Sheet Hello, I have a sub & function where I email the First (column headers) and Last Row of the Sheet. (Sheetname=VoiceMailData) The email is working, and copying the data works fine. I just can't select the First and Last Row. I have 12 columns. Here is my code: Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Cells(Rows.Count, "A").End(xlUp).Row 'You can also use a range if you want 'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible) On Error GoTo 0 Thanks Stephan Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014704
  - 22
    - Hide VBProject while copying Forms & Modules to another workbook Hi, I have an automated application that modifies a number of workbooks by adding some forms and modules to each one, then saves and closes them. During this process, I use Application.visible = false to hide all the opening/closing workbooks so that the user can do other work without focus jumping to Excel. The problem is that for some reason, the Visual Basic window opens at some point and never closes. This defeats the purpose of making the application visible and I don't know why it's opening the window. It also confuses the user running the application because she doesn't know anything about excel. Any ideas on what command is actually making VBA visible? Any ideas on how to make it invisible? This one's a real bear because if I put a "Stop" or Pause in VBA, VBA automatically becomes visible so there's no way for me to see what's tripping the visiblity property. Thanks, MikeZz Here is the basic routine that copies over the forms and modules: Public Sub SendForms() errMaster = "SendForms: Sending Macros" srcWB.VBProject.VBComponents("frmNotFound").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr srcWB.VBProject.VBComponents("StartupForm").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr srcWB.VBProject.VBComponents("ExportCode").Export Filename:=sStr destWb.VBProject.VBComponents.Import Filename:=sStr Kill sStr Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Const DQUOTE = """" ' one " character Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkbook") Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CreateEventProc("Open", "Workbook") LineNum = LineNum + 1 .InsertLines LineNum, " ThisWorkbook.Sheets(" & DQUOTE & "VeryHidden" & DQUOTE & ").Visible = xlVeryHidden" LineNum = LineNum + 1 .InsertLines LineNum, " StartupForm.Show" End With End Sub Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014696
  - 23
    - If then else if problem for DA I know this macro doesn't work (bad syntax) and I also know there must be more elegant ways to do this. Kindly help. Sub HideUnusedHurdles() If Range("NumberOfHurdles").Value = 4 Then GoTo 100 Else If Range("NumberOfHurdles").Value = 3 Then Range("HideMonthlyRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyThreeHurdles").Select Selection.EntireRow.Hidden = True GoTo 100 Else If Range("NumberOfHurdles").Value = 2 Then Range("HideMonthlyRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyTwoHurdles").Select Selection.EntireRow.Hidden = True GoTo 100 Else If Range("NumberOfHurdles").Value = 1 Then Range("HideMonthlyRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True Range("HideQtrlyRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True Range("HideAnnualRowsIfOnlyOneHurdle").Select Selection.EntireRow.Hidden = True 100 End If End Sub Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014695
  - 24
    - formatting backcolor on Forms Control Button I was curious how you could color the backcolor of a forms button using VBA. The default is Grey, I would like to color it another color. Thanks -- daniel Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014683
  - 25
    - Multiple (ByVal Target As Range) Cancel each other out, as written My initial test of the paramenter worked out great!! Then I added the other paramaters. When putting a "1" in cell called TR1_AUG, into parameter 1 parameter 2 obviously cancels it out, so that no ranges are hidden. I currently have 5 parameters of which only ONE may be selected at any time. That one selection causes 8 ranges to be hidden. (although more ranges to hide will likely be required) It is imperative that the DE-selection of the given cell does UN-hide any previously hidden ranges. Can this be done in one go? if so, how? OR should it be set up as 5 independant subs? How would that have to be done? Thank-you If Range("TR1_AUG") = 1 Then 'Single Cell Rows.EntireRow.Hidden = False Range("TR2_PAD_S, TR3_FP16_S, TR4_FP26_S, TR5_FP36_S, Disch2_PAD_S, Disch3_FD16_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If If Range("TR2_PAD") = 1 Then Rows.EntireRow.Hidden = False Range("TR1_AUG_S, TR3_FP16_S, TR4_FP26_S, TR5_FP36_S, Disch1_AUG_S, Disch3_FD16_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If If Range("TR3_FP16") = 1 Then Rows.EntireRow.Hidden = False Range("TR1_AUG_S, TR2_PAD_S, TR4_FP26_S, TR5_FP36_S, Disch1_AUG_S, Disch2_PAD_S, Disch4_FD26_S, Disch5_FD36_S").EntireRow.Hidden = True Else Rows.EntireRow.Hidden = False End If Tag: Pivot Tables: Alternative to PivotSelect? Tag: 1014674

Hi guys

In VBA, we're accustomed to referencing ranges rather than selecting
them (which is of course what the macro recorder does). However, when
referencing ranges within a PivotTable, I don't seem to be able to
avoid PivotSelect and am wondering if there is one.

For example, in the following code, I am trying to format the total
columns of a ColumnField (pt is a declared PivotTable)

pt.PivotSelect "CategoryDemand[All;Total]", xlDataAndLabel, True
Selection.Interior.ColorIndex = 43
Selection.Font.Bold = True

So my question is, can I reference this range and format it, avoiding
PivotSelect?

Thanks in advance

Paul Martin
Melbourne, Australia

Top

Re: Pivot Tables: Alternative to PivotSelect? by Paul

Paul
Sun Oct 12 02:51:57 CDT 2008

I'm still looking for an answer, even if it's "no, you can't do it".

On Oct 8, 12:13=A0pm, Paul Martin <melbournef...@gmail.com> wrote:
> Hi guys
>
> In VBA, we're accustomed to referencing ranges rather than selecting
> them (which is of course what the macro recorder does). =A0However, when
> referencing ranges within a PivotTable, I don't seem to be able to
> avoid PivotSelect and am wondering if there is one.
>
> For example, in the following code, I am trying to format the total
> columns of a ColumnField (pt is a declared PivotTable)
>
> =A0 =A0 pt.PivotSelect "CategoryDemand[All;Total]", xlDataAndLabel, True
> =A0 =A0 Selection.Interior.ColorIndex =3D 43
> =A0 =A0 Selection.Font.Bold =3D True
>
> So my question is, can I reference this range and format it, avoiding
> PivotSelect?
>
> Thanks in advance
>
> Paul Martin
> Melbourne, Australia

Top