Set Pivot-Table Visible Items

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 range names with names in column heading Hi, Need some help. I have a sheet that has column headings along row 1. Would like to create range names with each column heading as the name. The number of rows will increase. Any suggestions? Tag: Set Pivot-Table Visible Items Tag: 998009
  - 2
    - use only numbers and '/' I have a textbox in a userform that I want to only accept numbers and '/' to enter dates. How do I do that? Thanks Tag: Set Pivot-Table Visible Items Tag: 998008
  - 3
    - Find entries in one column in another Hi, I have been trying to figure out a solution to this problem for days now and it is really bugging me... I have two columns with descriptions in. One column is part of a 'catalogue list' and one column is part of a 'things that have been ordered list'. For each item that is ordered the buyer needs to decide whether the item which they are ordering is on the catalogue or not. This is however not always done! This is where I come in. It is my job to search the list of items that have been ordered and using the description of the item (sometimes 10 words long) decide whether it is already on the cataolgue. This can be very time consuming and I was wondering if there was a VBA code which could speed up my manual process by comparing the two descriptions columns and matching up similar results. An example could look like... 'Things that have been ordered list' description **msc Breaded Fish Fillet Squares 70g 1x36pk 'Catalogue list' description Fish fillets 1x36pk, 70g Any help would be much appreciated...I am losing sleep over this!! Tag: Set Pivot-Table Visible Items Tag: 998002
  - 4
    - Andy Pope's button image editor; pasting icons to sheet then toolbar- why does image quality degrade? I used Andy Pope's add-in to save my custom button icons as .bmp files. To use them in my add-in, instead of trying to load them from the .bmp files directly, I used insert/image to put each .bmp on a worksheet in my add-in. I then use code to paste them to my new toolbar. The problem is that I seem to have quality problems in (at least) two places; when I insert a .bmp and enlarge it for analysis, the picture is similar to the original, but blurry (I'd expect it to just show a large 16x16 block of colors). Then when I paste it to the toolbar- then copy that image back into Andy Pope's editor, the end result shows that the image had further degraded. I'd like a clean (and transparent) .bmp on my final toolbar, but many google searches have turned up nothing. Is there a preferred approach to ensure that the button image quality remains stable/clean all the way through to the final toolbar? Thanks! Keith Tag: Set Pivot-Table Visible Items Tag: 997989
  - 5
    - Formula bar is hidden, how do I unhide it? Somehow I have hidden the forumla bar and cannot make it visible again. If you know how to "unhide" it, please respond. Tag: Set Pivot-Table Visible Items Tag: 997988
  - 6
    - Textbox value I have a textbox on a userform to input dates. How do I format the textbox to only accept dates in the form of mm/dd/yy? Thanks Tag: Set Pivot-Table Visible Items Tag: 997986
  - 7
    - Median of all combinations Hi, I have 5 numbers (can be with decimals) in column A (A1:A5). For these numbers, I want all combinations as well as the median of these combinations. I'm pretty far already. However, the median seems to be calculated wrong. Who can help me? Thanks, Andreas Sub test() ListCombos Range("A1:A5"), 3, "C:\Q1_3er.csv" End Sub Sub ListCombos(r As Range, ByVal m As Long, sFile As String) ' lists the combinations of r choose m to file sFile ' r is a single-column or single-row range Dim ai() As Long Dim i As Long Dim n As Long Dim sOut As String Dim sOutMedian As String Dim iFF As Integer If r Is Nothing Then Exit Sub If r.Rows.Count <> 1 And r.Columns.Count <> 1 Then Exit Sub n = r.Count If m < 1 Then Exit Sub If m > n Then m = n iFF = FreeFile Open sFile For Output As #iFF ReDim ai(1 To m) ai(1) = 0 For i = 2 To m ai(i) = i Next i Do For i = 1 To m - 1 If ai(i) + 1 < ai(i + 1) Then ai(i) = ai(i) + 1 Exit For Else ai(i) = i End If Next i If i = m Then If ai(m) < n Then ai(m) = ai(m) + 1 Else Exit Do End If End If ' catenate and write to file sOut = vbNullString Call Sort(ai) sOutMedian = median(ai) For i = 1 To m sOut = sOut & r(ai(i)).Text & "," Next i Write #iFF, Left(sOut, Len(sOut) - 1), sOutMedian Loop Close #iFF End Sub Sub Sort(Arr() As Long) Dim Temp As Double Dim i As Long Dim j As Long For j = 2 To UBound(Arr) Temp = Arr(j) For i = j - 1 To 1 Step -1 If (Arr(i) <= Temp) Then GoTo 10 Arr(i + 1) = Arr(i) Next i i = 0 10 Arr(i + 1) = Temp Next j End Sub Function median(Arr() As Long) Call Sort(Arr) If UBound(Arr) Mod 2 = 1 Then median = Arr(Int(UBound(Arr) / 2) + 1) Else median = (Arr(UBound(Arr) / 2) + Arr(Int(UBound(Arr) / 2) + 1)) / 2 End If End Function Tag: Set Pivot-Table Visible Items Tag: 997985
  - 8
    - compare oldvalue and new value of a cell Hi All, how to write a code to compare the value of a cell before it's being updated by user.If the new value is different with the new value,I want the cell background colour become different.But since I have many sheet in my workbook,I want the code become public and I try to keep the sheet clean from any vba code. Thank's Shiro Tag: Set Pivot-Table Visible Items Tag: 997979
  - 9
    - Get reference to a file opened in another instance of XL Using Office 2003; Generic: I need to loop thru all open MS-Excel files in another instance of XL with the object in mind of getting a reference to a particular file opened as a template. I can positively identify the template file by the contents of certain cells in the template. Specific: The user will be opening an Oracle Financials, Fixed Asset (CPR) ADI spreadsheet which always opens in a new instance of XL. I need to get a reference to this spreadsheet and populate it with data from an array. I can do all of this just fine, but it's the reference to the other instance that is a challenge. Any help would be appreciated. Tag: Set Pivot-Table Visible Items Tag: 997977
  - 10
    - Slow procedure I have modified a procedure I downloaded from Debra Dalgleish's site and show the detail as below. Basically it looks at a range of values then creates a named worksheet for each of these values if that worksheet does not already exist. If it does exist it just clears some ranges and copies in some filtered data. I would be grateful if someone could have a quick look through to see if I have put in anything in such a way that it would really slow the operation of the procedure. Don't get me wrong, the procedure does exactly what it is ecpected to do , it just seems to take a bit of time and I just wonder if there is anything slowing it. I am sorry I have notes above each operation as I am not the sharpest pencil in the box when it come to programming and I need to keep track of what I am trying to do. Don't spend a lot of time on it, as I say it works and is liveable with. Sub ExtractFields() Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("FieldMaster") Set rng = Range("DatabaseList") Application.ScreenUpdating = False r = Sheets("Entries").Cells(Rows.Count, "A").End(xlUp).row For Each c In Sheets("Entries").Range("A12:A" & r) ' check if sheet exists If WksExists(c.Value) Then 'Clear existing sheet areas if sheet already exists Sheets(c.Value).Range("B12:E15").ClearContents Sheets(c.Value).Range("B23:E28").ClearContents Sheets(c.Value).Range("J12:N15").ClearContents Sheets(c.Value).Range("J23:N28").ClearContents ' run advanced filter to get organic applications rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets(c.Value).Range("Q1:Q2"), _ CopyToRange:=Sheets(c.Value).Range("C11:E11"), _ Unique:=False Else 'If sheet does not exist add it Set wsNew = Sheets.Add ' enter after last sheet wsNew.Move After:=Worksheets(Worksheets.Count) ' name the sheet wsNew.Name = c.Value ' copy template to new sheet Sheets("FieldBase").Cells.Copy Destination:=wsNew.Range("A1").Cells ' Enter field name into FieldMaster for soils copy Sheets("FieldMaster").Range("AA2").Value = wsNew.Name ' Copy base soil to Soils sheet Range("SoilBase").Copy Sheets("Soils").Cells(Rows.Count, 1).End(xlUp)(2) ' enter sheet name in reference cell wsNew.Range("B2").Value = wsNew.Name ' put field name into filter criteria to allow for alphanumerics wsNew.Range("Q2").Formula = "=""=" & wsNew.Range("B2").Value & """" ' run advanced filter to get organic applications rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=wsNew.Range("Q1:Q2"), _ CopyToRange:=wsNew.Range("C11:E11"), _ Unique:=False End If Next ws1.Select End Sub Function WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) > 0) End Function Tag: Set Pivot-Table Visible Items Tag: 997974
  - 11
    - OldCell colour With below code,I want turn the OldCell colour to another colour if the cell's value was updated by user, if it's not updated the colour is xlColorIndexNone,how to modify the code? Thank's in advance. Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Static OldCell As Range If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = xlColorIndexNone End If Target.Interior.ColorIndex = 6 Set OldCell = Target End Sub Tag: Set Pivot-Table Visible Items Tag: 997970
  - 12
    - Problem setting each parameter in an Array = Nothing I want to reset each parameter in an Array = 0 or " ". Why can I not make myDes = Nothing. I am getting an error below. If I set myDes = 0 everything works fine, but it will not work when myDes = Nothing Sub ResetVariables() Dim myBOM_Des As Variant Dim myDes As Variant ' row location of part on "Parts List" myBOM_Des = Array(AlumFace_Row, Texture_Row, Primer_Row, Paint1_Row, Paint2_Row, Paint3_Row, Paint4_Row, Vinyl1_Row, _ Vinyl2_Row, Vinyl3_Row, Vinyl4_Row, ClearVinyl_Row, Ink_Row, Plastic_Row) ' sets row locations to Nothing For Each myDes In myBOM_Des ERROR => myDes = Nothing Next myDes. End Sub -- Cheers, Ryan Tag: Set Pivot-Table Visible Items Tag: 997968
  - 13
    - Mutually Exclusive Option Boxes I need to set up 3 option boxes that are mutually exclusive. When I click on one option box the other two should be unchecked etc.etc. How do I go about doing this - I had a look through the forum but I can't seem to find a solution. Thanks Tag: Set Pivot-Table Visible Items Tag: 997963
  - 14
    - Why doesn't this work? Hi All, The following code snippet is invoked from my personal.xls workbook. What l am attempting to do is select any excel file in any directory and then create a .xls file for each worksheet in the selected workbook into a 'TEMP' directrory The code is failing at the 'sh.Copy' line. Can anybody tell me what is wrong please? FileNameOnly & DirOnly are simply functions that l have used many times to extract the relevant information from the string returned by GetOpenFilename Additionally l need to add some code to check if the 'TEMP' directory has already exists, any ideas, example code would be gratefully appreciated. Sub CreateXLFiles() Dim afile As String 'Source workbook to be rebuilt Dim adir As String 'Directory of sheet files Dim sh As Worksheet afile = Application.GetOpenFilename(, , "Select the source file", , False) Application.ScreenUpdating = False adir = (DirOnly(afile) & "\" & FileNameOnly(afile) & "-TEMP") MkDir adir Workbooks.Open afile, UpdateLinks:=False For Each sh In Workbooks(FileNameOnly(afile)).Worksheets sh.Copy <<<<<<<<<<<<<<<ERROR HERE ActiveWorkbook.SaveAs adir & ActiveSheet.Name & ".xls" ActiveWorkbook.Close SaveChanges:=False Next Workbooks(FileNameOnly(afile)).Close SaveChanges:=False End Sub Regards Michael Tag: Set Pivot-Table Visible Items Tag: 997950
  - 15
    - Printing style I write the C# module that open the workbook and send it to printer. Is there any posibility to detect that the worksheet was designed to print in Landscape or Portrait.? Tag: Set Pivot-Table Visible Items Tag: 997940
  - 16
    - PLEASE HELP WITH AN SPECIAL AUTOFILL A good day ! I have in a sheet in entyre column BD , 1423 cells with array formulas ; the cells with formulas are from range BD91 to BD65536 , in every 46 row : BD91 , BD137 , BD183 , BD229 , BD275 , BD321, BD367 , BD413 , BD459 BD505, BD551 , BD597 , BD643, BD689 , BD735 , BD781 , BD827 , BD873 BD919 , BD965 , BD1011 .................etc...............BD65319 , BD65365 ,BD65411 BD65457 , BD65503 With usual macro I have 1423 lines in macro to do this autofill : Range("BD91").AutoFill Destination:=Range("BD91:BD136"), Type:=xlFillDefault Range("BD137").AutoFill Destination:=Range("BD137:BD182"), Type:=xlFillDefault Range("BD183").AutoFill Destination:=Range("BD183:BD228"), Type:=xlFillDefault Range("BD229").AutoFill Destination:=Range("BD229:BD274"), Type:=xlFillDefault Range("BD275").AutoFill Destination:=Range("BD275:BD320"), Type:=xlFillDefault Range("BD321").AutoFill Destination:=Range("BD321:BD366"), Type:=xlFillDefault Range("BD367").AutoFill Destination:=Range("BD367:BD412"), Type:=xlFillDefault Range("BD413").AutoFill Destination:=Range("BD413:BD458"), Type:=xlFillDefault ........ .........................................etc.............................................................................. Range("BD65411").AutoFill Destination:=Range("BD65411:BD65456"), Type:=xlFillDefault Range("BD65457").AutoFill Destination:=Range("BD65457:BD65502"), Type:=xlFillDefault Range("BD65503").AutoFill Destination:=Range("BD65503:BD65536"), Type:=xlFillDefault My goal is to get *THE BEST SPEED* for this autofill in this entyre column BD ; Please very much to provide me this kind of macro , which shall give me the MAXIMUM SPEED to autofilling the 1423 cells in entyre column ;(I use excel 2003 ). Thanks very much for your time Tag: Set Pivot-Table Visible Items Tag: 997932
  - 17
    - can same data in different cells be replaced by any formula Tag: Set Pivot-Table Visible Items Tag: 997927
  - 18
    - Copy From HTML prgramme figures I have copied a table (values) from a HTML program but when it come to add only it is adding half of it the rest when trying to add that is not adding, i have seperatly try to add those are not adding it show #VALUE! -- VJC Tag: Set Pivot-Table Visible Items Tag: 997926
  - 19
    - Detecting the change of a certain cell Dear All: Is it possible that someone can kindly tell me how to detect the change of a certain cell? environment is VSTO + VS 2008 + EXCEL 2007. the Globals.sheet1.Calculate += is successful but the Globals.sheet1.Change doesn't work. Could anyone provide a sample? FYI the code is written within a window Form. Best Regards, Fan Tag: Set Pivot-Table Visible Items Tag: 997923
  - 20
    - If one range changes, update the other Hi, With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub Tag: Set Pivot-Table Visible Items Tag: 997920
  - 21
    - Excel crashes Hi, I am using Excel 2007 on Windows XP. On the sheet I have a button (form button) that executes the macro below. The macro runs fine, the new workbook (myTemplate1.xls) opens up OK. The problem occurs when I close the workbook (myTemplate1.xls) Excel crashes. What am I doing wrong? By the way I have been using myTemplate (opening it manually) for years without a problem. It is only when I open it using the macro below that I have this problem. Thanks for any help Fred Sub Button432_Click() Dim PartNo As String PartNo = Trim(Sheet1.Range("C8").Value) If PartNo <> "" Then Workbooks.Add Template:="\\myServer\myDrive\myTemplate.xlt" End If End Sub Tag: Set Pivot-Table Visible Items Tag: 997919
  - 22
    - Email with Notes I am trying to setup to email a spreadsheet thru notes, I have everything worked out EXCEPT for the send to I have tried the following emailsendto=emailadd emailadd is a range in a spreadsheet (email b2) the format is "username@company.com,","username2@company.com,","username3@company.com" but it is only sending to the first name Now I could set the range to B2:b11, but not sure of the syntax any help would be appreciated Tag: Set Pivot-Table Visible Items Tag: 997918
  - 23
    - Compile Error: Procedure too large I am using the: Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) routine to run a series of macro's, but not i get the 'Too large error'. I was thinking if i break up the code and place it into various Modules, and using a 'Call" procedure to run the macro with this procedure instead. However when i tried this it does not run. Can i remove the 'Private' and use a 'Public' to get it to work? Tag: Set Pivot-Table Visible Items Tag: 997917
  - 24
    - If, then, for a long time I've asked several questions lately, and I'm sorry about that. I have a series of formulas that I could probably put into a very large macro, but I'm sure there's a way in VBA, but I can't figure it out. I have a very large spreadsheet to work with, but the formulas on most of them are either the same of similar. In one section I have a table that is renewed monthly. The table looks like this: Term Value 1 13905 2 12920 3 12015 4 11455 5 11095 6 11060 7 11200 8 11010 9 10580 10 10160 11 9705 12 9210 13 8665 14 7985 15 7375 16 6990 17 6730 18 6690 19 6745 20 6585 21 6280 22 5980 23 5660 24 5320 In addition there is a row of numbers that relate to this table that also changes monthly (it's used to keep track of the market value of a vehicle). The rows of data look like this: Aug-07 $18,848 Sep-07 $18,320 Oct-07 $17,792 Nov-07 $17,264 Dec-07 $16,736 1 Jan-08 $16,208 2 Feb-08 $15,680 3 Mar-08 $15,152 4 Apr-08 $14,623 5 May-08 $14,095 6 Jun-08 $13,567 7 Jul-08 $13,039 8 Aug-08 $13,905 9 Sep-08 $12,920 10 Oct-08 $12,015 11 Nov-08 $11,455 12 Dec-08 $11,095 13 Jan-09 $11,060 14 Feb-09 $11,200 15 Mar-09 $11,010 16 Apr-09 $10,580 17 May-09 $10,160 18 Jun-09 $9,705 19 Jul-09 $9,210 20 Aug-09 $8,665 21 Sep-09 $7,985 22 Oct-09 $7,375 23 Nov-09 $6,990 24 Dec-09 $6,730 25 Jan-10 $6,690 26 Feb-10 $6,745 27 Mar-10 $6,585 28 Apr-10 $6,280 29 May-10 $5,980 30 Jun-10 $5,660 31 Jul-10 $5,320 The August 07 amount is when it was put in service. We didn't really start keeping track until a few months later, which is next to the number 1. There is another cell that I put a date for the end of the month in, and I have several other cells that use that date in a vlookup table using the dates you see above (I've had to turn those formulas into VBA code, but that's another issue). Here's the deal. When it's July 2008 a formula is in place to move make various calculations, and the August 2008 cell should equal the first number in the first table above (in this case 13,905). In August 2008 I need the formula to carry down from July, and for September to then equal the first number in the table, October to equal the second number, etc. Is this something I can set up in VBA to do? I appreciate the help. -- Message posted via http://www.officekb.com Tag: Set Pivot-Table Visible Items Tag: 997907
  - 25
    - Sheet sub to fire after DV selection is changed I'm using the sheet sub below to auto-scroll horizontally In C3 is a formula: =ADDRESS(5,MATCH(C2,G5:HJ5,0)+6,4) In C2 is a DV which selection feeds into C3 for the resulting cell address Despite calculations set to auto mode, I find that after I select the DV, I still have to click inside C3, then press ENTER before the auto-scroll sub will work. How could the sub be amended to work straight off after the DV selection is changed? Thanks for insights ---------- Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Address = Range("C3").Address Then Application.Goto Range(Target.Value), True End If Exit Sub Handler: Dim msg As String Select Case Err.Number Case 1004 msg = "Probably invalid address" Case Else msg = "Unknown error'" End Select MsgBox msg End Sub -------- Tag: Set Pivot-Table Visible Items Tag: 997889
- Next
  - 1
    - Enquiries about calculation formula Is there a formula for the the following calculation (a to d)? I have tried the following way but not successful. =if(a2>0.01,"1","0",if(a2>100.01,"2","0",if(a2>200.01,"3","0".....continued to d) a) If the amount is greater than 0.01, than the rate is $1.00 b) If the amount is greater than 100.01, than the rate is $2.00 c) If the amount is greater than 200.01, than the rate is $3.00 d) If the amount is greater than 300.01, than the rate is $4.50 e) If the amount is greater than 400.01, than the rate is $5.50 Thank you very much for your response. Tag: Set Pivot-Table Visible Items Tag: 997881
  - 2
    - Adding Label Numerical Values correctly I am trying to ADD the values of 3 label Caption IF they are a Numerical Value. I have used the LONG approach below, but it only works on the provision that the user starts at the label63 then to the Label74 and thirdly to the Label87. But if the user starts at label74 or Label87, the below code does not add the values and place the result into Textbox64. How can i better code this to allow for ALL not matter what Labels are numerical, and total the values in TextBox64, without having to add every possible combinatiopn of the current code i have ? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = False And IsNumeric(Label87.Caption) = False Then TextBox64.Value = Val(Label63.Caption) If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = True And IsNumeric(Label87.Caption) = False Then TextBox64.Value = Val((Label63.Caption)) + Val((Label74.Caption)) If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = True And IsNumeric(Label87.Caption) = True Then TextBox64.Value = Val((Label63.Caption)) + Val((Label74.Caption)) + Val((Label87.Caption)) TextBox64 = Format(TextBox64, "0.00") ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CtM. Tag: Set Pivot-Table Visible Items Tag: 997872
  - 3
    - Subscript out of range error Hello, I have been trying to fix this error but I have no clue why it's not working. Any help would be greatly appreciated! I am getting "Subscript out of range error" when I execute the coding given below, only when I HAVE AN EXCEL INSTANCE OPEN. If I close all the excel instance and run the code again, it works just fine. Here is the coding: ------------------------------ Dim xlsApp As Object 'I included this ExcelIsOpen function to see if an excel instance is already open 'and if so, use that...but that doesn't seem to fix the issue If ExcelIsOpen Then Set xlsApp = GetObject(, "Excel.Application") xlsApp.Visible = True Else Set xlsApp = CreateObject("Excel.Application") xlsApp.Visible = True End If xlsApp.Workbooks.Open Filename:="C:\USERNAME\Import_UnmappedMoves.xls", Password:="password", WriteResPassword:="password" 'am getting the error when the next line executes Workbooks("Import_unmappedmoves.xls").worksheets("Moves").Activate ------------------------ Do you know where I am going wrong? Thanks in advance for your help! Tag: Set Pivot-Table Visible Items Tag: 997868
  - 4
    - Frame without background color (invisible)? I'm not sure if this is even possible but it is -- awsome! =) Is anyone aware of how to create a frame without any background color? The reason I ask is I'm trying to lay a frame over a photo / flash animation object and I can't seem to get it to work correctly. I know that you can do this effect with textboxes, pictures, etc... but not sure about frames... You guys rock! Thanks for helping us out all these times. Tag: Set Pivot-Table Visible Items Tag: 997867
  - 5
    - I hope to get many responses on this. I am currently in pain over the Sarbanes-Oxley rules and what they are requiring. I am not sure if I am so upset over the requirements or what appears to be manangements and Sarbanes auditors lack of knowledge of how to use excel. The Sarbanes people want to throw more people at every issue and my feeling is the real issue is their lack of knowledge of excel. Here is a general idea of my proposal. I feel it would probably take care of half of what they call deficiencies. My question is how secure it this: I have to send a pre formatted excel file "File01" to 10 subsidiaries so they can input thier month end financial data. The file has 12 sheets. I passworded each sheet and only made unprotected cells where I want input. I also passworded the workbook. I also passworded the file. Now to open the file I also send each user a second file "File02", it's sole purpose is to open the first file with code which the VBA is also passworded. What I am telling the Sarbanes-Oxley people is the file is secure and I will know if I get the correct File01 back becuase the first process when I open the file is to see if the file has a password. If they do not send me the correct file then either the file will not open because it has the wrong password or they used another file with no password which I will catch when I ask if the file has a password. I want to use this method because when I get the 10 files back I can do basically anything I want with the data using macros to automate the combing of data anyway that I want. Question: How secure is this method in that the user will have to use File02 as the opening file to force use of File01 that has file, workbook and worksheet protection. How easy would it be for someone to get past all that security? Note: I am thinking if they get past the file password it will not do them any good because they will not be able to get past the Workbook and Worksheet security. I will also be testing each files passwords for Workbook and Worksheet. Thank you for your comments. Steven Tag: Set Pivot-Table Visible Items Tag: 997859
  - 6
    - Comparing worksheets with non-identical rows of information Hi, I am a newcomer when it comes to functions and programming in Excel which makes this task all the more difficult. I have a worksheet that is updated continuously with new/added information and information that is deleted. This is an inventory worksheet with retail items listed in rows and aspects of its description located in adjacent cells. Basically, I cannot depend on an item to be located in the same row each time the worksheet is updated since things are added and deleted. What I would like to do is have the original (non-updated) worksheet be compared to the updated worksheet by: 1. Using the info starting from C3 down (the UPC number for the product) in the original worksheet and search for this number in the same column (C) in the updated worksheet. 2. Highlight any differences in columns G, H, I, for the row in question where the identical UPC numbers are found in both worksheets. (Highlighted in the original worksheet) This is a very involved task I would assume, so if an answer cannot be provided, I would certainly appreciate any suggestions regarding reference material. Thank you for your time Glenn Yamada Tag: Set Pivot-Table Visible Items Tag: 997856
  - 7
    - SQL ORDER BY issue I am creating a SQL SELECT statement in VBA that extracts data from a MS-SQL DB. All was going well, until I decide to put and "ORDER BY" statement at the end of the SQL string I was creating. Here is my code snippet: strTablein = "dbo.hpsc_application" strFieldin = "Planned_Obs_Date, " strFieldin = strFieldin & "Actual_Obs_Date, " strFieldin = strFieldin & "PlannedRetFYQtr, " strFieldin = strFieldin & "IT_Owner_L2, " strFieldin = strFieldin & "IT_Owner_L3, " strFieldin = strFieldin & "IT_Owner_L4, " strFieldin = strFieldin & "date_of_last_record_update" strWhere = "IT_Owner_L2 = 'my owner name field (data)'" strSQL = "SELECT " & strFieldin & " FROM " & strTablein & " WHERE " & _ strWhere & " ORDER BY Actual_Obs_Date DESCENDING" Debug.Print strSQL Set rs = con.Execute(strSQL, , 1) I am getting a sql error on the execute, stating there is an error "near DESCENDING". I cannot see what that would be. The output of the debug of strSQL is: SELECT Planned_Obs_Date, Actual_Obs_Date, PlannedRetFYQtr, IT_Owner_L2, IT_Owner_L3, IT_Owner_L4, date_of_last_record_update FROM dbo.hpsc_application WHERE IT_Owner_L2 = 'my owner name field (data)' ORDER BY Actual_Obs_Date DESCENDING In the WHERE clause, I am wondering if it is something with the single quote, or some such. Note, if I remove the "...ORDER BY ....." all the way to the end of the statement, it works completely fine (just not in the order I want). Was hoping another set of eyes might see an obvious error. Thanks! PatK Tag: Set Pivot-Table Visible Items Tag: 997855
  - 8
    - Moving Columns Macro Hi, there. I am attempting to set a macro to move columns in one spreadsheet to match the column heading order in a different spreadsheet. If the secondary spreadsheet always came in the same order, I could just move all of the columns once and record the macro. However, the secondary spreadsheet column headings are in no standard order once it is received, but must be in the same order as the primary spreadsheet when finished. Is there a way I can set up a macro to search for the column heading and then move it to the appropriate place to be in the same order as the primary spreadsheet? Tag: Set Pivot-Table Visible Items Tag: 997849
  - 9
    - Data sorting to a new sheet I'm not even sure this can be done. What am trying to do is sort data from Sheet 1 and place the sorted data on sheet 2 & 3. Here is what the data would look like: 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 What I need is all Rows with something in Address1 column goes to sheet1 (in order) and all Rows with something in column Strobe_circuit_info goes to sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3). ex. 3 NAC_110 V1-01 A1-01 30 NAC_110 V2-01 A4-03 57 NAC_110 V3-01 A1-23 so if the if there is a device V1-02 it would go in row 4 and so on, same for sheet 2 Is this possible or am i really reaching Thanks for your input Tag: Set Pivot-Table Visible Items Tag: 997847
  - 10
    - Macro not ending? I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam Tag: Set Pivot-Table Visible Items Tag: 997846
  - 11
    - delete alternate column How can I delete every other column (B, D, F.....) in Excel? Tag: Set Pivot-Table Visible Items Tag: 997843
  - 12
    - Control button display issue I am adding buttons with the following code. They are active and function but do not display. If I set them manually to "Text Only (Always)" via the customize menu screen, they display fine. I'm using XL2003. I am at my wit's end trying to do this programmatically. What am I missing? Thanks. .Controls.Add Type:=msoControlButton, ID:=2949, Before:=2 With .Controls(1) .Visible = True .Caption = "ReadFile" .OnAction = "thisworkbook.readfile" End With Tag: Set Pivot-Table Visible Items Tag: 997840
  - 13
    - AutoIncrementing a Column like a database in Excel 2007 Hi, We're going to use an SQLite database but we'd like to do all our data entry in excel. Then we'd like to save the sheets as .csv files and import the data into an SQLite database. For us to do this successfully we need to be able to AutoIncrement an id field in Excel like a database would. Does anyone know the best way to do this? Thanks, Keith Tag: Set Pivot-Table Visible Items Tag: 997835
  - 14
    - radio button with VB code Hello all, Iâ??m needing the following: 4 radio buttons, â??Defaultâ??, â??GTDâ??, â??ECOâ??, â??BOTHâ??. When running a VB code, I need to code to look at what button has been selected and call another VB code or in the case of â??Defaultâ?? it keeps running the code with out interruption. So, VB code is running, then it looks at the radio buttons, see that â??GTDâ?? is select and then calls another VB code, then it continues on with the rest of the code when the other code is finished. Thanks Tag: Set Pivot-Table Visible Items Tag: 997827
  - 15
    - VBA - Modify data in selection I need to concatenate a string in a column of a sheet based on user selection. Column L contains a string to which I need to add either "(SC)" or "(DR#)" where # is a number between 1 and 7. Ideally, the users can select the rows they wish to modify (be it 1 row or 1000), right-click the mouse and choose the appropriate modifier. In my code, Selekt is a public variable to hold the choice from the right-click. Note that there are two attempts to make this work with different coding. The first is from another forum and I think it is closer to doing what I need. The second is what I tried at the beginning. Here's the code I have so far: Private Sub InsertDR() Dim t As Long Dim rainj as range Dim ro as range Dim rev As String If Selekt = "0" Then For i = 1 To Selection.Rows.Count rev = CStr(Sheet1.Cells(ro, 12).Value) rev = rev & "(SC)" Sheet1.Cells(ro, 12).Value = rev Next ElseIf CInt(Selekt) < 8 Then rainj = ActiveSheet.Selection For Each ro In rainj rev = CStr(Sheet1.Cells(ro, 12).Value) rev = rev & "(DR" & Selekt & ")" Sheet1.Cells(ro, 12).Value = rev Next End If End Sub Any help here would be hot. Thanks! -- Adios, Clay Harryman Tag: Set Pivot-Table Visible Items Tag: 997826
  - 16
    - excel macro to open Sharepoint list in excel and save in my folde Hi I have created a share point list. and i want to save as excel file in my folder I need help to write macro to open Sharepoint list in excel and save in my folder. so when i click on button (macro) it should do this process. Thanks! Tag: Set Pivot-Table Visible Items Tag: 997821
  - 17
    - Creating a working program / application from an Excel sheet I have a complex spreadsheet i am looking to market, however i dont want to use Excel as the platform. How would i go about creating an actual application from the spreadsheet that can stand alone without the need for Excel?. Is there a way to easily create an application from a complex sheet? Can anyone help? Thanks, Roger Tag: Set Pivot-Table Visible Items Tag: 997817
  - 18
    - macro to compare few columns data of two spreadsheet Hi ! I want to compare Few columns of two spreadsheet using macro. Can anyone help me writing code Thanks! Tag: Set Pivot-Table Visible Items Tag: 997812
  - 19
    - Find End of Range, I am hoping to find a way of finding the end of a used range (different on each sheet), move over five columns, and color the entire range, in column X, from cell X6 to this cell lower boundary of the used range. I can find the end of the used range with this line of code: Cells(Rows.Count, "S").End(xlUp).Select I need to do this for several spreadsheets in a workbooks that I just created; I am using a For...Next Loop and this part is fine...just need to get a handle on this colored range thing. -- RyGuy Tag: Set Pivot-Table Visible Items Tag: 997806
  - 20
    - Help to code Macro to Copy fron one sheet and paste in other sheet Hi I have tow excel sheets.- sheet1 and sheet2 in sheet2 i have all columns from sheet1 + additional columns. in sheet1 i have unique Action# column which does not have dublicate data. and that ACtion# is in sheet2 (as i have copied Sheet1 data). So what i want when some one adds or update data of sheet1, i want to run a macro which adds new data(rows) into sheet2 and updates data as well (update means- if someone updates the data in sheet1, it should update those data in sheet2 when you run a macro) So need help to code macro which adds data in sheet2 and if data is there then update it. I appreciate for any help. Thanks! Tag: Set Pivot-Table Visible Items Tag: 997801
  - 21
    - count formula problem I have a formula in excell to count the amount of cells that contain numbers. I use this formula =COUNT(A1:A90) and if the count was 30 I would get 31. I have two other sheets in the same file where the calculations are correct. The only way I can get the correct calculation is to do this formula =COUNT(A1:A90)-1 Any ideas? Tag: Set Pivot-Table Visible Items Tag: 997798
  - 22
    - Transparent .XLA? Take this in the context of a total noob's groping around. Looks to me like .XLA's (or, at least, their use) are user/PC-specific and not application/spreadsheet-specific. i.e. If user Smith is able to use it on his desktop PC, he may not be able to use it with the same spreadsheet on his laptop. i.e. For a spreadsheet to be able to use code in Whatever.xla, each user's MS Office installation has tb configured to point to it as an Add-In. If I'm correct so far, I'm looking for a way to deploy 70+ identical .XLS files to 70+ different users but have all 70+ .XLS' use the same code module - so if we need to change the code we're not up a creek. We can live with the .XLA living on a LAN drive (i.e. a user working offline cannot get to it). Any solution that requires anybody to do anything on the user's PC is out of the question. It has tb 100% transparent. The first thing that comes to my mind is to have each .XLS' Workbook_Open event somehow check to see if the user's PC/MS Office has an Add-In pointing to our .XLA and, if not, create one in such a way that it points to the common version on a LAN server without copying it to C:\Program Files\WhereverAddinsLive The second thing that comes to mind is doing the same check to see if there's an Add-In and creating one if not - but pointing it to the user's C: drive and unconditionally copying the .XLA to somewhere on the user's C: drive if the LAN is available, and just quietly not doing so if the LAN is not available. Seems like this would give a little more portability, yet still let us push changes down to each user. Are either of these "good practice"? If not, can somebody suggest something? -- PeteCresswell Tag: Set Pivot-Table Visible Items Tag: 997794
  - 23
    - Cycle through all worksheets in a workbook I have some code which automatically copies and pastes data off all workbooks (1 sheet each) in a folder. How about if I want to make it cycle through multiple worksheets within a workbook before closing it? I made a few modifications to this loop, but it is not working. Anyone know why? Do While sFil <> "" Workbooks.Open sPath & sFil For Each w In ActiveWorkbook.Worksheets <---added this With ThisWorkbook.Worksheets("Bulk") <---and this k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row n = k + 4 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial End With <---added this Next w <---and this oWbk.Close True <--should this be False I appreciate your help as always. Arlen Tag: Set Pivot-Table Visible Items Tag: 997790
  - 24
    - time box I want to make a box which is in military time tied back to a range of cells. Can it be done and how could it be easily done. Thanks for help -- ED Tag: Set Pivot-Table Visible Items Tag: 997789
  - 25
    - Search for cell color, copy values in other columns Hi everyone, I am trying to put together a tool that can can search, column by column, for a certain background/fill color (yellow), and then copy/paste the value/text in various columns of the same row for each instance of yellow background colored cells, into another worksheet. I think I know how the first part of this problem can be solved (searching for and identifying the background color cells), but how can I code to copy/paste the values in specific cells? Tag: Set Pivot-Table Visible Items Tag: 997788

With ActiveSheet.PivotTables("AssetPT").PivotFields("Cost ctr")
.PivotItems("12476").Visible = True
.PivotItems(DataArray(X, 1)).Visible = True
end with

Why would the line with "12476" work and the line immediately below, where
DataARray(x,1) equals "12476" give me this error:

Error (1004) Unable to get teh pivotItems property of the pivotfield
class?????

Top

Re: Set Pivot-Table Visible Items by Debra

Debra
Fri Jul 25 14:59:07 CDT 2008

Manually or programmatically set the field to Manual sort, to prevent
that error:

With ActiveSheet.PivotTables("AssetPT").PivotFields("Cost ctr")
.AutoSort xlManual, .SourceName

Mike H. wrote:
> With ActiveSheet.PivotTables("AssetPT").PivotFields("Cost ctr")
> .PivotItems("12476").Visible = True
> .PivotItems(DataArray(X, 1)).Visible = True
> end with
>
> Why would the line with "12476" work and the line immediately below, where
> DataARray(x,1) equals "12476" give me this error:
>
> Error (1004) Unable to get teh pivotItems property of the pivotfield
> class?????

--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Top