วีดีโอ

The issue arises because Excel stores dates as serial numbers, and the MONTH(TODAY()) function only returns a number between 1 and 12, which doesn't directly match your named range or columns representing specific months and years. Here's how you can fix the issue and create a working hyperlink that dynamically jumps to the current month's column: 1. Your column headers should match the month and year format, such as Jan-24, Feb-24, etc. Ensure these are formatted as TEXT, not DATES. 2. Use a formula to match the header with the correct column. Assuming your headers are in row 1, here's how you can write the hyperlink formula: =HYPERLINK("#" & ADDRESS(1, MATCH(TEXT(TODAY(), "mmm-yy"), 1:1, 0)), "Jump to Current Month") By generating the correct header dynamically and ensuring it matches the actual column labels, this approach should solve your problem.

Thank you so much for sharing this! Your approach is incredibly efficient and dynamic, and it offers a great solution for advanced users who want minimal manual intervention. I appreciate you breaking it down step-by-step for clarity-this is a valuable addition to the tutorial! The method I demonstrated was designed to introduce foundational Excel functions step-by-step, catering to users who are still building their confidence with these tools. Thanks again for sharing your expertise-it's always great to see different approaches to solving Excel challenges!

Thanks for pointing that out - that's a great observation, and it's definitely something to consider when working with large datasets!

@ExcelWithBrainBell I have tried this with a data set of approximately 20x150 and it went horribly slow on my i3 laptop.

@@ManthaarJanyaro Thank you for your feedback! The slowdown occurs because the Worksheet_SelectionChange event triggers every time the active cell changes, and with a large data set, this can impact performance on systems with limited resources. To optimize the code for larger data sets, you can: 1. Limit the Range: Ensure the "If Not Intersect()" statement restricts the formatting to only the necessary range. 2. Minimize Formatting Actions: Reduce the number of formatting changes applied within the Worksheet_SelectionChange event. For instance, avoid using complex gradient fills or multiple style changes. 3. Consider Conditional Formatting: For static formatting rules, conditional formatting may be more efficient and doesn't rely on VBA.

Thank you!

Thank you so much! I'm glad you enjoyed the content.

Thanks for your interest! I'll upload the exercise workbook as soon as possible. Stay tuned!

I'm glad you found the video helpful! I'm planning to do a deeper dive into Excel tables in the near future.

@ExcelWithBrainBell So noble of you, I look forward to it.

You're welcome! I completely understand-it can feel unnecessarily complicated at first. Creating a custom worksheet template should be as straightforward as creating a new workbook. I'm glad this tutorial helped make it easier for you!

Thank you!

Hi, It is a built-in feature of Excel's context menu and part of the newer Excel interface. Unfortunately, VBA doesn't offer direct control to remove or hide this search box, as it's not treated like a regular command or menu item. If you're customizing the context menu with VBA, this search box will still appear at the top by default, and, to my knowledge, there's no native VBA solution to remove it at the moment. The best approach is to continue with your customizations below the search box. Thanks.

Visual Studio makes designing custom Excel ribbons a breeze. You can use the drag-and-drop Ribbon Designer to quickly set up tabs and controls, and easily switch to the code editor to add functionality in C# or Visual Basic, depending on your preference. Visual Studio provides Office-specific templates that streamline setup, supports direct debugging within Excel to test changes instantly, and includes a one-click publishing tool to create an installer for easy distribution. With support for multiple languages and powerful design tools, Visual Studio is an excellent choice for developing Excel add-ins.

To make your custom default worksheet template open when you insert a new worksheet, or to have your custom worksheet templates available in the Insert dialog box when you right-click on a sheet tab and choose Insert, you need to save your templates in a location recognized by Excel, such as the XLSTART folder. Once your template is saved in the XLSTART folder, it will automatically open when you insert a new worksheet or appear in the Insert dialog box when you right-click on a sheet tab and choose Insert.

Sometimes, Excel needs to be restarted for new add-ins to appear correctly in the toolbar. Close and reopen Excel to see if your add-in shows up. Here are a few things you can try to resolve the issue: 01:45 Ensure the Add-In is Enabled: - Go to the Developer Tab (or File > Options > Add-Ins). - Click Excel Add-ins. - Make sure your add-in is checked in the list. If it's not in the list, click Browse, navigate to where you saved your add-in, and select it. - Close and reopen Excel. 02:04 Check if You Created a Button for the Add-In: Creating an add-in alone doesn't automatically create a button for it in the toolbar. To create a button for your add-in on the ribbon, follow these steps: - Go to File > Options > Customize Ribbon. - Select Macros from the dropdown on the left, find your macro, and add it to a new custom group on the right. 3. Go to the Add-Ins menu, remove the add-in, then add it again.

Thank you for your feedback! I'm glad to hear the code is working well for you with the button setup. No problem at all regarding the addition of "Application.ScreenUpdating = False" that's a great improvement for optimizing performance when working with multiple files. It's always helpful to reduce screen flickering during operations like this.

Thank you sir

Are you looking to display the name of the currently active file in the status bar while multiple Excel files are open? If so, are you referring to updating the status bar dynamically as you switch between files, or is there a specific way you want this information presented?

Thanks! Glad you found it helpful!

Thanks 🔥

Here’s how you can modify the current VBA code: Sub CopyRangeFromSelectedWorkbooks() Dim wb As Workbook Dim sourceRange As Range Dim destRange As Range Dim selectedFiles As Variant Dim lastRow As Long Dim activeSheet As Worksheet ' Set active sheet Set activeSheet = ThisWorkbook.ActiveSheet ' Ask user to select workbooks selectedFiles = Application.GetOpenFilename( _ FileFilter:="Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", _ MultiSelect:=True, Title:="Select Workbooks") ' Check if the user selected any files If IsArray(selectedFiles) Then For i = LBound(selectedFiles) To UBound(selectedFiles) ' Open each selected workbook Set wb = Workbooks.Open(selectedFiles(i)) ' Set source range from Row 17 to Row 24 in each workbook Set sourceRange = wb.Sheets(1).Range("A17:D24") ' Check if rows 17 to 24 are blank in the active sheet If Application.WorksheetFunction.CountA(activeSheet.Range("A17:D24")) = 0 Then ' If rows 17 to 24 are blank, paste there Set destRange = activeSheet.Range("A17:D24") Else ' If rows 17 to 24 are filled, find the next empty row lastRow = activeSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1 Set destRange = activeSheet.Range("A" & lastRow & ":D" & lastRow) End If ' Copy source range to destination range in active sheet sourceRange.Copy destRange ' Close the source workbook without saving changes wb.Close False Next i Else MsgBox "No files selected." End If End Sub

​@@ExcelWithBrainBell Dear Sir , Many Many thanks for Try to support , your Code not success work , Thank you . May Allah bless you .

I apologize that the revised VBA code did not meet your requirements. Based on your previous comment, here's what I understand you need: 1. You want to copy data from rows 17 to 24 in Sheet1 of each workbook (e.g., workbook1.xlsx, workbook2.xlsx, etc.), and then paste that data into your active worksheet (e.g., Main.xlsx). 2. The data should initially paste into rows 17 to 24 of the active sheet (e.g., Main.xlsx). If rows 17 to 24 are already filled, the next set of data should be pasted into the next available rows, starting after the last filled row. I’ve tested the code on my system, and it works as described above. However, if this still isn't matching your expectations, could you please clarify your requirements further? This will help me better understand the specifics of your request and provide an accurate solution.

@@ExcelWithBrainBell Dear Sir ,Please See The Video Of my working file , Need Two Destination : First 17 to 24 Rows . Second : After 30 rows , present Destination code is okay for second option , I am lucky to your feedback

brainbell.com/?p=3695#download

@@ExcelWithBrainBell Dear Sir , It is High Quality & scholars type Video Tutorial , I am proud To getting The Tutorial , I am grateful to you , The Video Tutorial Helpful For me , So , A Small Requiest To you , to developing Some Option : Your Code & tutorial is Ok , but My requiest Is : 1. Set source range in each workbook from - Row 17 to 24 in Blanck cell . (End(xlUp).Row + 1) from Row 17 to 24 2. when blank cell will filup then past will in last offset row ( Present code destination ) it is your pleasure & free time ,no Emargency . thanks Mostain Billah Dhaka, Bangladesh.

@@ExcelWithBrainBell Dear Sir , It is High Quality & scholars type Video Tutorial , I am proud To getting The Tutorial , I am grateful to you , The Video Tutorial Helpful For me , So , A Small Requiest To you , to developing Some Option : Your Code & tutorial is Ok , My requiest Is : Set source range in each workbook from - Row 17 to 24 in Blanck cell . (End(xlUp).Row + 1) from Row 17 to 24 (Note: Rows 17 to 24 of my workbook are blank and there have rows 25 to 28 with data, ) when blank cell will filup then past will in last offset row ( Present code destination ) it is your pleasure & free time ,no Emargency . thanks Mostain Billah Dhaka, Bangladesh.

You are most welcome

May Almighty Bless you.

You're welcome

You can add more information directly on Sheet1 to make stock checking easier. Here's how: 1. In the next column, you can display the stock from Sheet2 (the warehouse) using this formula: =INDEX(Sheet2!C:C, MATCH(B2, Sheet2!B:B, 0)). 2. In another column, you can calculate the stock difference between Sheet1 and Sheet2 with this formula: =C2 - D2. This way, you can easily see how much stock is in the warehouse and the difference, even if Sheet2 has more entries or is in a different order. Thanks again for your kind words!

@@ExcelWithBrainBell Thank you so much, the formulas work a treat! Keep up the good work 👍

Welcome

Will upload soon

th-cam.com/video/Q4RZfIOQVj4/w-d-xo.html

You're welcome!

Thank you for your question! You can achieve this by using VBA: Sub ExtractString() Dim fullString As String Dim firstWord As String Dim secondPart As String Dim thirdPart As String Dim fourthPart As String Dim dashPos1 As Integer Dim dashPos2 As Integer ' Get the string from C5 and remove the ".ord" part fullString = Replace(Range("C5").Value, ".ord", "") ' Extract the first word (W5606) firstWord = Split(fullString, " ")(0) ' Find the position of the first dash dashPos1 = InStr(fullString, "-") ' Extract the second part (ENG 04 03 11) secondPart = Mid(fullString, Len(firstWord) + 2, dashPos1 - Len(firstWord) - 2) ' Extract the third part (BANGLADESH) dashPos2 = InStr(dashPos1 + 1, fullString, "-") thirdPart = Trim(Mid(fullString, dashPos1 + 1, dashPos2 - dashPos1 - 1)) ' Extract the fourth part (DHAKA) fourthPart = Trim(Mid(fullString, dashPos2 + 1)) ' Place the extracted parts into the specified ranges Range("A29").Value = firstWord Range("B29").Value = secondPart Range("C29").Value = thirdPart Range("D29").Value = fourthPart Range("C5").Value = fullString End Sub To use this code, follow these steps: 1. Press ALT + F11 to open the VBA editor. 2. Go to Insert > Module to insert a new module. 3. Copy and paste the code above into the module. 4. Close the VBA editor. 5. Run the macro ExtractString from the Excel workbook. This will extract the string as you described and place them in the specified cells (A29, B29, C29, and D29). I hope this helps!

Thank you very much, I am grateful to you, may Allah bless you ❤️

=IF(AND(COUNT(B2:B16)=COUNTA(B2:B16),COUNTBLANK(B2:B16)=0),SUM(B2:B16),"Fill with numbers") Thank you for your suggestion! The formula you provided is indeed a great improvement. By using AND along with COUNT and COUNTA, it ensures that all cells contain numeric values and that none are blank. This adds an extra layer of validation, making it even more robust. I appreciate your input and will consider including it in future tutorials. Keep the ideas coming!

Yes, it's definitely possible to extend the formula to check the two lists by the product code before highlighting quantity differences! Simply use the MATCH function to find the corresponding product code in Sheet2 and then use the INDEX function to retrieve the quantity. Here's how you can do it: 1. Select the quantity range on Sheet1. 2. Go to Home > Conditional Formatting > New Rule. 3. Use the formula =C2<>INDEX(Sheet2!C:C, MATCH(B2, Sheet2!B:B, 0)) to highlight differences. This way, you can accurately compare quantities even if the lists are in different orders. Let me know if you have any further questions!

Thank you so much for your kind words! I'm really glad to hear that you found the tutorial helpful and easy to follow. Your feedback means a lot to me and motivates me to keep creating content. If you have any more questions or topics you'd like me to cover, feel free to let me know. Thanks again for your support!

Thanks for your question! To highlight differences in stock levels between two sheets in Excel, you can use conditional formatting. Here's how: 1. Select the Quantity Range on Sheet1 (e.g. C2:C20). 2. Go to Home > Conditional Formatting > New Rule. 3. Choose "Use a formula to determine which cells to format" and enter =C2=Sheet2!C2 for matching quantities, formatting with a green fill color. 4. Add another rule for differences with the formula =C2<>Sheet2!C2, formatting with a red fill color. This way, matching quantities will be green and differing quantities will be red. Check out the video I created on this topic: th-cam.com/video/VCmF_saotw4/w-d-xo.htmlsi=U4-BMpISpcitzkfP

Thank you so much! I'm glad you enjoyed it!

You are so welcome!

If you need to protect formulas in a cell where data entry is required, one approach is to use separate cells for formulas and data. Place the formula in a different cell and reference the data cell. Then, lock the formula cell and protect the worksheet as shown in the video. This way, users can still enter data without altering the formulas.

Glad you found it interesting! Thanks for watching!

Yes, the voice is generated by AI. Glad you liked it!

Thank you! Happy to hear you enjoyed the VBA solutions.

Glad you liked it!

Thank you for your kind words! I'm glad you found the video helpful.

@@godsontc243 my name is Brian

@@godsontc243 and thank you very much I will give it a try!

Thank you for your feedback! The methods shown should generally work in Excel 2021. If you're experiencing issues, here are a few things to check: For Hiding Rows and Columns: - Ensure that you've selected the correct rows or columns to hide. - After selecting, right-click and choose 'Hide' from the context menu. You should not be able to scroll beyond the visible rows and columns. For VBA Scroll Area: - Make sure you have saved the workbook as a macro-enabled file (*.xlsm). - Check that the code Me.ScrollArea = "A1:H13" is placed correctly in the Worksheet_Activate event of the correct sheet. If these steps don't resolve the issue, it could be specific to your Excel setup or configuration. Please let me know more details about the problem you're encountering, and I'll do my best to help you troubleshoot it!

Before hiding the Rows and Columns I will “freeze the panes” in the lower right cell so the view cannot be scrolled off screen.

Great tip! Freezing the panes is an excellent way to lock the view and keep the important part of the sheet visible while restricting scrolling. Thanks for sharing that suggestion!

Thank you so much! Your feedback means a lot!

Press Ctrl+A or click the small triangle in the top-left corner of the worksheet to select everything, then - Right-click on any column letter and choose "Unhide." - Right-click on any row number and choose "Unhide."

Thanks a lot 😊

If for some reason you need to clear the scroll area limits, you can remove the restriction with this statement: Me.ScrollArea = "" Or simply create a standard macro that will reset your scroll area back to the full sheet: Sub ResetScrollArea( ) ActiveSheet.ScrollArea = "" End Sub For detail, please visit: brainbell.com/excel/limit-the-scrolling-range.html#macro