User Profile
rzaneti
Iron Contributor
Joined Jan 19, 2023
User Widgets
Recent Discussions
Re: How to Automate a Rostering System
MarTyr109 , About the automation, as mentioned by NikolinoDE , you may use VBA or Office Scripts, and both tools are connected to another Microsoft solution, which is the Power Automate, which contains several connectors with other Microsoft products and third part ones. As in Excel, the Power Automate community is very active and will be able to help you with the automation share of your solution. Here are some links about it: Blog post that compares VBA with Office Scripts: http://digitalmill.net/2023/06/10/office-scripts-the-new-vba/ Power Automate documentation: https://learn.microsoft.com/en-us/power-automate/ Access to Power Automate Community: https://powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_ForumsRe: NEED HELP!! If Cell contain specific text then move row to another sheet
Hi Kenneth_Manuel , I tried to run some tests with your code and everything seems to be working properly. The only thing is that you are applying your second IF statement (the code block 'K = K - 1') after deleting the row with "ETA Not Passed" , "Delivery ETA Not Passed" & "Schedule Not Passed". I don't know if this is the intention, but I flag it, as you are applying the K decrement to the row below the keywords. To try to find a solution, you can debug it "step-by-step", by opening the VBA interface, selecting your method and clicking F8 (and then F8 again for each line of code). This approach will basically run your program line by line, so it will be easy to find any unexpected behavior. Also, if you prefer, you can send us a small sample of your workbook (you can populate it with fake data), as your particular workbook design may be causing this issue.Re: Tracking line graph
kentleywiltshire4 I'm not sure, but I believe that Excel is not able to snapshot any data and register only the changes. Considering the file that your shared, I'm afraid that it will be very difficult to catch these changes with the current Sheet structure. If you have the option to modify the workbook structure, maybe it will be helpful to catch these changes.Re: Tracking line graph
kentleywiltshire4 , Just to confirm: you want the line chart to present the cumulative totals from each day? So if you got a total savings of 100 in Jan 31st and a total savings of 50 in Feb 2nd, you want the line chart to present Feb 2nd as 150, correct? If yes, you first have to transform your savings column in numeric data (right now it is as text, at least in the file that you sent) and summarize the entries by day. You can do this by using a pivot table or the UNIQUE function connected to a SUMIF (example of the attached file). Please, confirm if my assumptions are correct and let me know if the file that I sent you is in the right direction for solve your problem.Re: Add multiple sheets of existing excel sheet data in one workbook
Hi Sadia_Ayaan , It is possible to automate this task by using a VBA Macro (if you use Excel on Desktop). I will ask you some questions to we build a solution together: - Does this first workbook have other worksheets additionally to these 25? If not, the work is even easier. - Will these 25 worksheets keep the same name in the new workbook? If not, how will you name them? If you prefer, you can send a sample Workbook just to I check it in more details. You don't need to share the original file, just a sample with fake data that illustrate how your sheets are distributed.Re: Vs Last Week Sales Formula
Hi Stevenfeasey , I tried a solution with some nested IFs. It is probably not the best way to solve it, but I think that it can help with your case. I'm sending you a file simulating it on Sheet2 (there is also a sheet simulating the previous week). Let me know if it works for you, or if you have any question or need additional help.Re: Loop Through Worksheets and Counts Populated Rows
Hi, ClaireMcF . There are some possible reasons for an error at this line of code. Could you please send an image of the error message? One of the possible problems is the error 6 (overflow), which will happen if we have an empty Sheet or if you have a specific Sheet with a large number of rows (more than 32,767 rows). Can you confirm if you have any of these cases in your file?Re: Loop Through Worksheets and Counts Populated Rows
Hi ClaireMcF , I'm sending you a file to you test if the Macro works for you. The code is the following: Sub count_indexes() Dim ws As Worksheet 'All Worksheets variable Dim CurrentSheetName As String 'Variable to store the name of the Sheet that the program is reading Dim CurrentSheetCount As Integer 'Variable to store the qty of rows of the Sheet that the program is reading Dim RowToInsert As Integer 'Variable to store the row to be filled in INDEX Sheet RowToInsert = 3 'Initially we set the row to be filled in INDEX Sheet as 3. Feel free to change it according to your use case For Each ws In Worksheets 'Loop to run all of the Sheets from the file If ws.Name <> "INDEX" Then 'Apply this block of code only if the current Sheet is not INDEX CurrentSheetName = ws.Name 'Store the Sheet name (note that, in your print, the stored name would be 'Andrew_Mohan', and not 'Mohan' Sheets(CurrentSheetName).Activate 'Activate the Sheet Range("A2").End(xlDown).Activate 'Check the range with populated rows CurrentSheetCount = ActiveCell.Row - 1 'Store the row number and subtract 1, as we have to discount the "A1" cell Sheets("INDEX").Activate 'Activate Index Sheet Cells(RowToInsert, 2).Value = CurrentSheetName 'Store Sheet name into column B (you can change it according to your use case) Cells(RowToInsert, 3).Value = CurrentSheetCount 'Store the qty of populated rows into column C (you can change it according to your use case) RowToInsert = RowToInsert + 1 'Increase the row counter for the next run of the loop End If Next ws End Sub You probably will have to make some changes to adapt it to your use case, so I added some comments to make this task easy. Please, let me know if it works for you and, if you have any question or need any help for change it, let me know.Re: save file without overwriting existing file in VB Macro
Hi BillZab , I added some comments to the code to make it easy to find where to edit it. Option Explicit Dim HasWeekFile As Boolean 'Variable to check if the file name exists Sub SavePDF() Dim MyDir As String Dim MyFileName As String Dim MyWeekNo As String HasWeekFile = False 'The standard value of this variable should be False MyWeekNo = "4" 'Fake value assigned here. You probably extract it dynamically in your code. Make sure to edit it when importing. MyFileName = "Week " + MyWeekNo & Format(Range("A2").Value, "-yyyy ") 'Build the file name with "Week " (constant), MyWeekNo (variable) and the year value from cell A2 from current Sheet MyDir = "C:\Users\" + strUser + "\OneDrive\Documents" 'Define the dir where the file will be saved (and where the existent files will be searched) ChDir MyDir Call GetFilesInFolder(MyDir, MyFileName) 'Call GetFilesInFolder method If HasWeekFile = True Then 'In case of HasWeekFile be assigned as True, file name will receive the "(1)". It is possible to edit it to create a (2), (3) and so on dynamically MyFileName = MyFileName & "(1)" End If ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _ MyFileName, Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=True End Sub Sub GetFilesInFolder(FolderPath As String, FileName As String) Dim oFSO As Object 'Variable to run the File System Dim oFolder As Object 'Variable to access the folder (MyDir, in this code) Dim oFile As Object 'Variable to run each file inside MyDir Dim i As Integer 'Index for iteration. You can remove it, as it won't be used in this code. Dim PdfToFind As String 'Name of the file with .pdf extension PdfToFind = Trim(FileName) & ".pdf" 'Remove the empty spaces in start/end of file name (MyFileName) and add extension .pdf Set oFSO = CreateObject("Scripting.FileSystemObject") 'Initialize File System Set oFolder = oFSO.GetFolder(FolderPath) 'Set MyDir as path to search the files For Each oFile In oFolder.Files 'Loop that iterate all files inside MyDir If oFile.Name = PdfToFind Then 'Check if the current file name is the same of MyFileName plus .pdf and, if yes, assign variable HasWeekFile as true HasWeekFile = True End If i = i + 1 'You can remove it, as it won't be used in this code. Next oFile 'Jump to the next file. End Sub Just to confirm: your expected output is something similar to the image below, where your current sheet will be stored as "MyFileName (1)", right? Let me know if you need any additional help.
