1. M

    VBA Code s not working even though the sum of filtered values are equal to 0.

    Set SumRange = ReconWS.Range(Cells(2, AmountFilter), Cells(ReconRows, AmountFilter)).SpecialCells(xlCellTypeVisible) If Application.WorksheetFunction.Sum(SumRange) = 0 Then ReconWS.Range(Cells(2, MatchedItem),Cells(ReconRows,MatchedItem)).SpecialCells(xlCellTypeVisible).Select...
  2. G

    Summary non numeric data

    Hi Community, I´m working on a summary sheet (by date and within a time span (between 2 dates)) and all fine with numeric values, but i can´t figure out how to aproach the columns with cells/columns containing text. For some columns i have to get all disctinct values in one cell for a given...
  3. J

    VBA - Copying rows in between ranges

    Good Afternoon, I've been working on a file that has been automated in the past with simple formulas or just by linking cells to other sheets. In this case, I'm having trouble programming a macro, by using a button, to copy a specific table in between ranges. The ideia is to click the button...
  4. D

    Selecting Tab with VBA that has a Dynamic Tab Name

    Hi, I have looked for a solution to this and am unable to find one. I have a series of tab with a number code and month such as "12345 - April", and for subsequent months it will be "12345 - May" with the month changing each time for each of the number code tabs. I am referencing to the tabs...
  5. M

    Apply Formula To Specific Column for Multiple Sheets in a Filtered Table

    Hi all, I have an issue with renaming cells in a filtered table and would be grateful for some help from the Excel community! Objective: 1. For specific sheets in the workbook, apply a filter in column B:B for a specific number of names Company (Parent) 2. Apply a formula to change the Parent...
  6. J

    Copying rows in between ranges with VBA

    Good Afternoon, I've been working on a file that has been automated in the past with simple formulas or just by linking cells to other sheets. In this case, I'm having trouble programming a macro, by using a button, to copy a specific table in between ranges. The ideia is to click the button...
  7. C

    Macro not recognising sub name

    I'm trying to write a macro to filter a pivot table based off a cell value. I've adapted the code from this SO post: How to control Excel PIVOT tables from cell values with VBA And this is mine currently Sub Worksheet_SelectionChange(ByVal Target As Range) 'This line stops the worksheet...
  8. U

    Variable in Shell function

    Hello, I am trying to use the Shell function and trying to make it dynamic by adding a string variable in the file path but I am having trouble doing so. Here is what I have so far: Dim last as String Dim batchfolder as String last = Missing_Files.Range("B" &...
  9. A

    Saving content of individual cells within a column as separate JSON files

    Is there a way to create a collection of JSON Files in a folder based on the content of individual cells in a column in excel? So basically, I have a spreadsheet with a column starting with cell B12 to B26. Each cell contains a JSON script. I want a VBA that would convert each of these cells...
  10. O

    VBA to paste from Clip Board onto an Outlook email.

    Hi Guys I need help with the VBA. Here is what I have so far. THe first few lines copys some rows in excel (some rows are hidden, filtered out in a filtered cells by the way) I don't know how do I paste this into the body of the email?? I tried searching online but it came back with really...
  11. C

    Combobox value to display adjacent cells in textboxes

    I have the following code from a previous project i worked on and tried to modify it to find the value of a cell in column A and if the value matches, then display the two adjacent cell values in the two textboxes i have on the user form: Dim i As Integer Dim arrIn As Variant Dim arrOut() Dim j...
  12. D

    Convert Entire Sheet to a Named Table for a Pivot Table

    Hi guys, I have looked online to try and find some solutions but I keep getting an error. Perhaps I am messing up with the syntax. I want the vba to go to the worksheet titled "Index", and then to convert all of the data on that sheet into a table called "Source" (similar to going to the...
  13. W

    VBA Code to get distinct count with muliple criteria

    Can some one help me with a VBA code that gives distinct count of data in in "I" Column of sheet1 (dynamic) after filtering "Temp" and "Tem" in A column and "MX" in E column. I have a code as below which gives distinct count from "I" column but not able to add the filters. Would be great if get...
  14. S

    Help with Find command - Match names on multiple tabs

    Hi there, new member here - self taught excel vba so still a beginner and hoping someone may be able to help point me in the right direction or be able to offer code which could work with what I need. I am working on a spreadsheet that has to be completed quarterly so am looking to automate a...
  15. S

    Ambiguous name detected unable to run code

    Private Const AuditSheets As String = "|Sheet3|" ' Must start and end with the "|" character Private Const AuditRange As String = "$C$2:$G$32" ' Change the tracked range as necessary Private Const LifeSpan As Double = 0.0013 ' 24 hour lifespan Private NextTime As Date Private Sub Workbook_Open()...
  16. E

    Index / match with several criteri

    Hello Guys, I m trying to do Index / match with several criteria but the formula is not working. {=INDEX(PointageStructure!$G$2:$G$1000,MATCH(1,(D2=PointageStructure!$B$2:$B$1000)*("Sub-task"=PointageStructure!$C$2:$C$1000)*("Analyste"=PointageStructure!$M$2:$M$1000),0))} Do you have any clue...
  17. A

    How to allocate amount based on Payment Start Date and End Date thru VBA

    Hi, I'm using a formula to allocate the amount evenly based on the frequency of payment, due to numerous projects it is quite difficult to change the reference for each project, how we can achieve the same through VBA. The results are in column G to J.
  18. E

    Substitute error

    Hello Guys, I m trying to get this forumla below working but i keep getting this error: Unable to get the Substitute property of the WorksheetFunction class Range("M" & cell.Row).Value = Right(cell.Value, Len(cell.Value) - Application.WorksheetFunction.Find("@"...
  19. S

    Occasional runtime 1004 error

    Hi Folks, I'm running the below code...and it works fine...some of the time. Other times I get a runtime error but I can't figure out how to resolve it; any tips? The bit where I get an error is; For Each shp In ActiveSheet.Shapes If Not Intersect(Range(shp.TopLeftCell...
  20. J

    VBA code to locate specific already filtered columns by name and paste into a new workbook

    Hi All, So to give some background, i have a large data set (60k plus rows and 90 plus columns) that i need to filter and create a new workbook using specific columns. The size of the file changes daily, and columns are taken out and added. So i had to keep updating the previous macro i had...

Some videos you may like

This Week's Hot Topics