Help with Excel report creation for review results from multiple worksheets...
Windows 11 MS Office 365 Cloud Accessed from Citrix desktop I've been working on this project for a while and have a tracking spreadsheet for several review categories, each on its own worksheet tab. I have a results page that gives me the data I need. The table on the RESULTS tab contains the results for the reviews and marks them as either having 'Not Met', 'Met' or 'Exceeded' the goal for each category. The final piece of the project is giving us problems. I received a great deal of help with it from @HansVodelaar here on the forum and he got very close to solving it. I've included the spreadsheet with the final attempted 'Result' tab. Here is what I'm trying to do now... Automatically generate a simple report Based on the generated date on 'RESULTS' for the selected Rep That lists all records, Labeled by review category (see review tabs), From the designated start date to designated end date, Only for the categories with scores designating them as either 'Not Met' or 'Exceeding' the goal. So far, the 'Report' tab works to list the records. However, there are a couple of problems with it. I can't seem to successfully copy the formula to the master workbook, which holds the same structure, but is not redacted. The returned data, as is, only separates the categories by a blank line, but does not identify to which review tabs (categories) the results belong. Any help offered is greatly appreciated. Hans suggested that the solution may require use of Power Query. So, if anyone has an idea of how I can accomplish this report without having to go into each tab to filter, sort, format and print, it would be totally awesome!!
Issue with saving Macros Personal Macro Workbook in the startup folder must stay open for recording
Hi, I'm hoping one of the Excel gurus can assist me with this issue I am suddenly having with Macros. Suddenly all of the macros I had saved and have been using have dissappeared. I had these Macros set up in a tab and would just click the macro for the job I was running and it would format my document accordingly. As everyone knows, this saves a significant amount of time. Why this happened, I don't know. The only option now is to rebuild them. This is the process I'm doing to rebuild them: Developer tab Record Macro In Record Macro Box Name Macro Store macro in: Personal Macro Workbook Click: OK Error Message Returned: "Personal Macro Workbook in the startup folder must stay open for recording." I don't know how to get this Personal Macro Workbook to open. I thought that this workbook would automatically open at the start up of Excel. This glitch has caused me so much time and energy that it has actually put me behind in my work. I've checked the path and the "Personal.xlsb" file is in the XLSTART folder.
Getting updated data from a Microsoft Form's SharePoint Excel Repository using VBA.
I want to grab data from an Excel file in SharePoint that is synced to a Microsoft Form and put it into a different file using a VBA macro, but I don't get the updated data. I have to manually access the Excel file that is a repository of the Microsoft Form Responses and let it sync to update. Then I can run the VBA. But I want to cut that manual step out. How can I do this?? I've tried many things (Power Automate, VBA updates, etc.), but no luck so far. I saw that I may have to use forms inserted through Excel online to get a file that refreshes immediately after each Microsoft Form submission, but that would mean I have to rewrite the Form and the VBA I currently have.
Excel shows value in cell, but VBA finds it empty
I’ve written a macro to compare a list of files and mark duplicates. It fails on the third name in the list, because activecell.value=‘’ (Null) is True, but I can see ‘01 in the cell. Why does VBA find that the cell is empty? How can I change the macro to work round this? Thanks in advance, SteveToggling autosave on saves despite saving has been disabled in VBA
I have an Excel spreadsheet that is used to calculate certain data and give a printable report. For the report patient information must be given. That information must not be saved. To accomplish that I've put following code into ThisWorkbook, and it works mostly as needed: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True 'Cancels any request to save the file End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ThisWorkbook.Saved = True 'Tells Excel that the file has already been saved (this prevents Excel from requesting that you save the file when you close it) End Sub And autosave has been disabled with code: Private Sub Workbook_Open() If Val(Application.Version) > 15 Then If ActiveWorkbook.AutoSaveOn Then ActiveWorkbook.AutoSaveOn = False End If Yet, when autosave toggle is clicked on, Excel saves the file. If the autosave is left turned on, after few seconds it displays the message, and doesn't allow saving any more, but at the moment the autosave is turned on, the file is saved. I've found a workaround. If I mark the file as final version, it can't be saved, but then it also needs additional click of the "Edit anyway" button before any data can be inserted. The file is available to other users via SharePoint, I can't manage file permissions. Is there any other way of dealing with this?
Run Multiple sets of data through set formula's
Hello, First off thank you for taking the time to look at this question though I would not be at all surprised if I am completely wrong about what I actually need. One of my colleagues and I have been working on a calculator of sorts for and we're able to get the result we wanted when you run one set of information through it at a time. However, what we want to be able to do is run multiple sets of data through it otherwise it is not the time saver we are looking for. Here are some screen shots: This is not real data as I am not sure if that would get me in trouble and I made the layout basic just to illustrate what I am trying to do. The Orange boxes are the information you would enter, so the artice, site and so on. The blue information are the result we receive back from the formula's we have already entered obtained from a data sheet we have setup. I think the tricky part is when you fill in the orange information, data is pulled from our other sheet which fills out this table: That's what make it hard to do multiple at once as the table as far as I can figure out, can only be populated one-by one. The goal would be able to upload any number of sets of those article, site, vendor and QTY's groups, and be able to simultaneously run them all to gather the needed information to vastly speed up this process. If providing more details on any of the other formula's we have will help, please let me know and again, any advice would be appreciated.Vba
You can achieve this by writing a VBA macro to open the source workbook, copy the second row from the specified sheet, and paste it into the next available row in the destination sheet. Here's the VBA code for your requirement: VBA Macro to Copy and Paste Data Sub CopySecondRow() Dim srcWb As Workbook Dim srcWs As Worksheet Dim destWb As Workbook Dim destWs As Worksheet Dim lastRow As Long Dim srcFilePath As String Dim destFileName As String ' Define file paths srcFilePath = "C:\Users\YourUsername\Desktop\new folder 16\1-2days allocation.xlsx" ' Update with the correct path destFileName = "y1737.xlsx" ' Open the source workbook On Error Resume Next Set srcWb = Workbooks.Open(srcFilePath) If srcWb Is Nothing Then MsgBox "Source workbook not found!", vbExclamation Exit Sub End If On Error GoTo 0 ' Set source worksheet Set srcWs = srcWb.Sheets("21 allocation") ' Activate the destination workbook Set destWb = ThisWorkbook ' Assuming the macro runs from 'y1737' workbook Set destWs = destWb.Sheets("allocation") ' Find the last used row in the destination sheet lastRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1 ' Copy the second row (A2: last column) srcWs.Rows(2).Copy ' Paste into the next available row in the destination sheet destWs.Cells(lastRow, 1).PasteSpecial Paste:=xlPasteValues ' Enter "y1737" in the adjacent column destWs.Cells(lastRow, destWs.Cells(1, Columns.Count).End(xlToLeft).Column + 1).Value = "y1737" ' Close the source workbook without saving srcWb.Close False ' Clear clipboard to release memory Application.CutCopyMode = False ' Notify user MsgBox "Data copied successfully!", vbInformation End Sub Steps to Use the Macro 1. Open the "y1737.xlsx" workbook. 2. Press ALT + F11 to open the VBA Editor. 3. Go to Insert > Module. 4. Paste the above VBA code. 5. Update the srcFilePath with the actual path of your "1-2days allocation.xlsx" file. 6. Run the macro (CopySecondRow). This macro will: Open the "1-2days allocation.xlsx" workbook. Copy the second row from "21 allocation". Paste it into the next available row in "allocation" of "y1737.xlsx". Enter "y1737" in the adjacent column. Close the source workbook without saving changes. Let me know if you need any modifications!Help with with Lookups etc.
Hi I’m looking for help as I’ve exhausted my knowledge and cannot sort this out. I have a spreadsheet with columns A-L. The sheet goes from A2:L90 (see attachment) Columns A and B are surname and first name. Columns C and G:H contain the roles held by those people. They may have several roles. A particular role may be in any of those six columns. All the different roles have their own individual empty worksheet, with a tab naming the role, that will hold the data found in the LookUp exercise. I want to have a macro that enables me to click on a role name (e.g. “Band Member” or “Role A”) from a dropdown list containing all the different roles. Then I need a function (I have tried HLookup, VLookup and Xlookup with no success, but I’m obviously doing something wrong) that can look across the master spreadsheet, and identify, say, all band members (or whatever role I click on from the dropdown list) that have that role shown in any of the columns C or G-K. It then needs to copy those names onto (in this case) the “Band Leader” worksheet (see attachment). I’m totally flummoxed on how to do this after hours of trying to the best of my limited ability. I would be most grateful to be pointed in the right direction.
