Macros en Excel 365 HOME
Buen dia. Agradezco un apoyo a mi siguiente problema. Tengo Office 365 HOME.. estoy tratando de grabar Macros y realizo los ajustes y corre correctamente Pero el problema es salir del archivo y volver a abrirlo.. ya que sale un error siempre Y no se guarda la macro.. He realizado lo mismo con Office 365 PRO PLUS y funciona sin problema la apertura. Hay que activar algo en la licencia Office 365 Home para grabar macros o NO ES POSIBLE! Gracias
Workbook_Open() fails to open
I built a macro that uses the function Workbook_Open() and then executes stuff which, as I understand it, should kick off as soon as the spreadsheet it opened. I've saved the spreadsheet as .xlsm and I can run the macro manually but it doesn't run itself when the spreadsheet is opened. I think the spreadsheet was originally created using 2003 and I'm using it in 2010. Could that be the problem?
Show filtered data in one spreadsheet clicking buttons in another
Morning, champions! Two spreadsheets in one workbook. In one spreadsheet I have thousands of rows. I have identified about 350 groupings of these rows and by clicking one of 350 corresponding links in an accompanying index spreadsheet (in same workbook) I want to show only the filtered rows in the first spreadsheet. I imagine I would tag the grouped rows with a numerical identifier. However there may be occasions where the same row falls into different groupings of rows so I don't know if this kind of straightforward identifier would work. Desired process User selects index spreadsheet Finds required story ID Clicks corresponding link or macro button Original spreadsheet appears automatically filtered to display only rows corresponding with Story ID. Is this possible? Or could the desired data simply appear in third spreadsheet that would in effect act as a view of filtered data? Thank you so much.
If cell value contains specific text do nothing any other value copy cell
I am using the formula below which works fine but I need to add to it repeating the process a few times for different cells (17 cells in total) If a cell H20 on worksheet "Estimate Parts Lookup" contains the text "Non Found" then the no change is made to cell E49 on worksheet "Estimate" If H20 on worksheet "Estimate Parts Lookup" contains any value other than "Non Found" the value is copied to cell E49 on worksheet "Estimate" Sub Copypartsprices() With Sheets("Estimate") If Sheets("Estimate Parts lookup").Range("H20") = "Non found" Then End End If Range("E49").Value = Sheets("Estimate Parts lookup").Range("L2") End With End Sub I now want to repeat the process above but adding to the process so that as well as the above it also checks to see if cell H21 on worksheet "Estimate Parts Lookup" contains the text "Non Found"as above if it does I want to make no change to cell E50 but again If H21 on worksheet "Estimate Parts Lookup" contains any value other than "Non Found" the value is copied to cell E50 on worksheet "Estimate" The below does not work but is what I have so far! Sub Copypartsprices() With Sheets("Estimate") If Sheets("Estimate Parts lookup").Range("H20") = "Non found" Then End End If Range("E49").Value = Sheets("Estimate Parts lookup").Range("L2") If Sheets("Estimate Parts lookup").Range("H21") = "Non found" Then End End If Range("E50").Value = Sheets("Estimate Parts lookup").Range("L3") End With End Sub In all I will repeat this process 17 times as below. Hope this makes sense E49 =H20 Unless H20 ="Non Found" E50 =H21 Unless H21 ="Non Found" E51 =H22 Unless H22 ="Non Found" E52 =H23 Unless H23 ="Non Found" E53 =H24 Unless H24 ="Non Found" E54 =H25 Unless H25 ="Non Found" E55 =H26 Unless H26 ="Non Found" E56 =H27 Unless H27 ="Non Found" E57 =H28 Unless H28 ="Non Found" E58 =H29 Unless H29 ="Non Found" E59 =H30 Unless H30 ="Non Found" J49 =H31 Unless H31 ="Non Found" J50 =H32 Unless H32 ="Non Found" J51 =H33 Unless H33 ="Non Found" J52 =H34 Unless H34 ="Non Found" J53 =H35 Unless H35 ="Non Found" J54 =H36 Unless H36 ="Non Found" Thanks in advanced Markmacro to add rows How enter realtive reference after row inserted?
Dim MyRange As Range Dim Icounter As Long ' Set MyRange = Range("C4:D14") ' For Icounter = MyRange.Rows.Count To 2 Step -1 ' MyRange.Rows(Icounter).EntireRow.Insert MyRange.Rows(Icounter).EntireRow.Insert 'Step5 Incrament the Counter Down Next Icounter End Sub
Extract parameter from web query
I have created a web query to look up the users from different groups in a website and saved the query to my desktop. There are a lot of groups that I need to track the users from, and I only need to change part of the web link to access those users. I created a macro to get the web query I saved on my desktop and changed the query parameters to my group names (now each chunk of users are linked to its group name which is the parameter). Then the macro will go to that link to get the users from that specific group for me. After the query has copied the users from the website to excel, I need to have the group name in the column next to the users column(for visualization purposes). Because every time my queries refresh, they insert cells for new users, and I will have to manually update the group name column. I am wondering if there is a way that I can extract the query parameter out or if there is a better way to do it. Column B will be the input of my web query, and Column C will be the parameters and I want to have them updated accordingly when some users have been added to the group and create new rows. My macro to trigger web query: With ActiveSheet.QueryTables.Add(Connection:= _ "FINDER;C:\Users\Desktop\query.iqy", Destination:=Range("$A$2") _ ) .Name = "query" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = _ """ctl00_ctl00_ContentPlaceHolderMain_ContentPlaceHolderSearchResult_ctl07_ObjectGrid""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End SubIs this a problem and if so how can I fix it?
While running a macro in my Office 365 Excel workbook I noticed many of these popup formatting tags that one usually doesn't see once they've pasted the values and clicked out of the cell. However, while the macro is running, the contents of the active worksheet disappears except for a few buttons embedded on the worksheet and all of these formatting popups appear. See the attached photo. I am just wondering if they can cause performance problems and if I can somehow clean them up as I no longer need them once my sheet is formatted the way I want it?
Creating Dependent Searchable Lists (List Box Dependent on Combo Box)
Hi All, I am not entirely sure how I should go about accomplishing this because I believe there are a few ways to do this. I have a list of data similar to below: 0582 051 0582 954 0582 725 1275 654 1275 777 5290 936 5290 003 5290 051 5290 923 5290 000 Basically, I want to create dependent list boxes, combo boxes, or drop down lists. I feel as though combo boxes would be the best because the first box is going to be populated with over a thousand codes (first column) so it would be best if the codes were searchable. Depending on the choice selected in the first box, only 20 or less options will appear as choices in the second box. However, the second box needs to allow for multiple selections. A list box would probably be the best option for this. How could I create a list box dependent on a combo based on the setup of the data above? In addition, would the second box populate another cell with its data? For example, all of the codes that are selected in the second box should appear in the cell next to it. If there is any more information I can provide, please let me know. Any help is appreciated! Thanks, Anthony Smith
