Forum Widgets
Latest Discussions
Excel / VBA - filter data and save in new file
Hi All, I need some help with Excel / VBA. I have a dataset with a column with different categories and want to filter on each category and save the filtered data in a separate file. This way the other data will not be visible in that data. I have tried ChatGPT/Grok but it just not seem to work. After a number of attempts, please see the VBA. The macro only creates Cat_A.xlsx and skips the rest with a "No data found" message. This is strange as the rest of the categories do have data. Do you think you can help me? =========================================== Sub ExportEachCategoryToFile() Dim wsSource As Worksheet Dim wbDest As Workbook Dim lastRow As Long Dim dataRange As Range, visibleRange As Range Dim dict As Object, cell As Range Dim cat As Variant Dim fileName As String Dim outputPath As String Set wsSource = ThisWorkbook.Sheets("Data") Set dict = CreateObject("Scripting.Dictionary") ' Define the path to save files outputPath = ThisWorkbook.Path & Application.PathSeparator ' Determine the data range lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row Set dataRange = wsSource.Range("A1:E" & lastRow) ' Get unique categories from column C For Each cell In wsSource.Range("C2:C" & lastRow) If Not dict.exists(cell.Value) Then dict.Add cell.Value, 1 End If Next cell ' Loop through each category For Each cat In dict.keys ' Clear any previous filters If wsSource.FilterMode Then wsSource.ShowAllData If wsSource.AutoFilterMode Then wsSource.AutoFilterMode = False ' Apply filter on Category (column 3) dataRange.AutoFilter Field:=3, Criteria1:=cat ' Get visible rows On Error Resume Next Set visibleRange = dataRange.SpecialCells(xlCellTypeVisible) On Error GoTo 0 ' If we have data, export If Not visibleRange Is Nothing And visibleRange.Rows.Count > 1 Then Set wbDest = Workbooks.Add(xlWBATWorksheet) visibleRange.Copy Destination:=wbDest.Sheets(1).Range("A1") wbDest.Sheets(1).Columns.AutoFit fileName = outputPath & "Cat_" & cat & ".xlsx" Application.DisplayAlerts = False wbDest.SaveAs fileName, FileFormat:=xlOpenXMLWorkbook wbDest.Close SaveChanges:=False Application.DisplayAlerts = True Else MsgBox "No data for category '" & cat & "' – skipping.", vbInformation End If Set visibleRange = Nothing Set wbDest = Nothing Next cat ' Clear filters If wsSource.FilterMode Then wsSource.ShowAllData MsgBox "All category files saved successfully!", vbInformation End Sub =========================================== Thanks, Naveen
Move columns with excel office script
I have a table with a number of columns, and I want to use the column names to reorganize them. Specifically moving the "Property Class" column to position 0 in the table, and the "Property Description" column to position 1. Is anyone able to help with the office script code?
Tooltip
Hello. I have imported the map in Excel in svg format. Now I want it to appear in tooltip format when it comes to regions. Which you have seen in Power BI. My question is, is it possible to do this in Excel? And if so, does the information in the tooltip also update automatically when the data is updated?
Preventing duplicate selections
Hi all! What I'm trying to do: Once a company is selected on one employee's sheet, it's removed from the dropdown lists on all the others, preventing duplicate selections. Background: I'm builidng a workbook with 18 sheets- one per employee. Each sheet has the same structure: column A contains a dropdown list of client companies, pulled from a master list on a separate sheet (Company List). When an employee selects a company, related contact info (name, phone, email) auto-fills using XLOOKUP. My setup so far: - The Company List sheet stores the master list of companies and contact info. - Dropdowns in each employee sheet (column A) are already pulling from that list. - Selection triggers auto-fill of contact details (working fine)
Conditional Formatting Rules for Icon Sets
Hi, The following pivot table has been created using Conditional formatting Icon Sets : However, the Default Formatting Rule applied by Excel is not understood. Following is the screen shot of Formatting Rules: Under the Formatting Rule Section: 01. The Green Icon is applicable when the value is >= 67 % 02. The Yellow Icon is applicable when value is < 67 % but >= 33% 03. The Red Icon is applicable when the value is less than 33%. The above Rule is clear but in my following pivot table data I have not achieved 67% or 33% of Total Revenue. Pls take a look of the screen shot : In Column E , Sales_Amount / Grand Total does not achieve 66% or 33%. All values are less than 33%. But Icons in Col B are appearing in Red, Green and Yellow. Pls let me know what is the logic behind the application of Icon Sets in Col B (Sum of Sales Amount). However, Same Default Formatting Rule is applied in Col D (Sum of Dollar per Hour). Pls see the screen shot below: However, it is not clear on which amount 67% and 33% are getting calculated. In Pivot Table CALIFORNIA has Dolloar Per Hour ( Sales Amount / Contracted Hours) 68.30 and it is getting Yellow Icon. Pls let us know the logic / Calculation of : 67% and 33% for Dollar per Hour. I have also attached the Excel file for reference. Regards, Somnath6309
sumifs doesnt work because of power query
Hi All, I've been trying to automate some regular reports using power query. The queries for the different reports are working and everything is fine, however I want to use those query tables and summarize them in my main report. The problem is that, i need to use a sumifs formula but it's not working. The formula that has always been used up until now with the old method of working is - =SUMIFS(SCRAP!$K:$K,SCRAP!$P:$P,"DTC*",SCRAP!$E:$E,"Job or Schedule",SCRAP!$B:$B,"<="&DATE(YEAR(SCRAP!$B$2),MONTH(SCRAP!$B$2),P$5)), where K:K is Value, P:P is Items, E:E is Source and B:B - dates and P5 is today's date (at the time of making the report) I think the issue is with column B (dates). For some reason, power query seems to "bug" the column with the dates and I've tried everything and it's still not working. Tried fixing it from power query editor and on paper it should be fixed but its not. Also, tried doing that in excel but still nothing. Any advice or a workaround would be greatly appreciated. Thanks in advance.
Excel form - may need VBA?
Hi all, New to this forum, but here goes: Have a complex Excel workbook (on Sharepoint). We need a better way to do data entry but with certain restrictions Want the data entry form to default to current date; how to enter a function into entry form? Want the form to behave according to data restrictions (pull-down) in the master Excel database sheet. The restrictions are on a separate tab How to do calculations in entry form (e.g. starting number plus quantity equals ending number; onlt start and end get populated back into database
combo bubble plot
Hi all I recently took part in a fauna survey of a meadow, where we reviewed one 1m square sites in a number of locations across the meadow to assess its health. I would like to present this data using the actual X and Y coordinates of the survey points and then have some representation of the over all health at that point. I thought a good way to do this would be using a bubble plot. With the coordinates being the X and Y and the health being the size of the bubble. That way we could get a visual representation (map) of the health across the site and potentially be able to compare future surveys be adding other data series for other surveys . The idea being that this might help us understand if some areas of the meadow healthier than others eg northwest corner or how things change over time. To help make this clearer I would like to do a couple of extra things to the chart and I just wondered if someone could given me a steer on how to do the following if possible: I would like to draw the meadow boundaries onto the same plot using the x and y coordinates so either using a scatter or line plot. So this would require a combo of scatter/line and bubble is this possible unless I can connect a separate series of bubbles with fixed size of 1% with lines. From a response in the Questions area it has been proposed that I grab a screen grab of another chart and use this as a background. which might make aligning difficult . So can I suggest that it might be worth looking at is this might be a future development Is it possible to get the actual bubble to colour code on the values in them ie RED if health under 30% to green if heath over 80% so that it is even clearer which are the healthier meadow areas. The proposed answer to this was to split the data into series for plotting with each series having a designated colour. Which works but would make plotting future years on the same graph difficult.
