1. T

    Copy/paste Filtered range into table VBA

    Hello, I am trying to filter data from one sheet and copy/paste that filtered data over into a summary sheet. I have 2 criteria that, if met, need to go into two separate summary tables. I am able to get the data filtered and copied, however, when it pastes into the respective tables, it is...
  2. M

    VBA If value exists in column of one table, offset the selection and copy that value into next available row in a different table.

    Hi all, I'm hoping you can assist me. On my worksheet ("MAIN") I have two tables. One table is "EmployeeList" and the other table is "AccountHolders". The "EmployeeList" is a frequently updated table, while the "AccountHolders" table is one that only updates when the "UPDATE BUTTON" (actual...
  3. B

    "PasteSpecial xlPasteFormulas" with relative reference with destination ListObject

    My goal is to copy-paste formulas from tableSource (a ListObject) to tableDest (also a ListObject) and to have those pasted formulas refer to tableDest, not to tableSource, which is what I get when I do a little something like this: ListObjSource.ListRows(1).Range.Copy...
  4. 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...
  5. L

    Looping through tables in vba, ignore empty tables?

    Hi all, So I have this following code (piece): <code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;"> Dim i As Long Dim LOC As Long If Not Sheet4.ListObjects("BLANKTable").DataBodyRange Is Nothing Then LOC = Sheet4.ListObjects.Count ElseIf...
  6. L

    To check if a table exists?

    Hi all, is there a simple code to check if a table exists in my document already? I am setting up a log, and every time someone opens my workbook, the username (its on a shared drive) and the date gets logged, and each macro (if they run it) logs a time, now the "If they run it" is my problem...
  7. E

    Type Mismatch using NumberFormat with DataBodyRange

    In the past I've done .Range.NumberFormat no issue, but having a problem using with .DataBodyRange . The following is what I have (with generic labeling): 'Code defining variables, etc with ws.listobjects(1) .DataBodyRange(r,c).NumberFormat = "0" 'Type Mismatch Error occurs end with...
  8. S

    Sort in excel table?

    Hi, I have a tables which has bunch of data which is bound to a list-object data-source, Data-source is List<My-class>. I am using WorkSheet.Change event to handle edits. Say if a cell in second row was edited, in Change event, I am checking for the row number of target and then access the item...
  9. H

    Cell references of range and listobject differ, looking for explanation and solution.

    I am trying to loop through the cells of several copied listobjects (table) in a workbook The problem I run into is that the cell references of the "(each) cell loop" are different from the listobject.range.cell/row references, and equal to the offset/references of the first cell of the...
  10. M

    Multiple tables on one spreadsheet. Adding data to particular table according to ListBox.Value selected

    Hi all, I have two tables on one spreadsheet (LiveProjects and TenderProjects) and I wish for a new row to be added with the data entered from the UserForm. The coding I have done for this works but what I particularly need is that depending on the 'Status' selected from the StatusListBox, the...
  11. M

    Update Data in Listobject

    Dear Experts What is the most elegant method to update data in a listobject (with a userform)? This is the table tblCustomers: <tbody> ID Name Street Postal Code Phone Mobile </tbody> The userform frmCustomer has: txtName , txtStreet, txtPostalCode, txtPhone, txtMobile Is this the...
  12. Chris The Rock

    VBA: How to check if a ListObject (Table) is being filtered on a specific field

    I'm editing a macro that came in a template downloaded from Microsoft. I want it to do two new things when a cell is double-clicked, provided the double-clicked cell is in the column I want filtered: 1) Filter that table on the value in the double-clicked cell. 2) UN-filter that table on the...
  13. M

    List Column Ranges

    I am attempting to apply xlTotalsCalculationSum to a range of cells in a table, but I cannot seem to get the range entered properly. Here is my current code. ActiveSheet.ListObjects("TenantIncome").ListColumns("Period2").TotalsCalculation = xlTotalsCalculationSum...
  14. TysonC

    adding comments to ListColumn tables in Excel with Access VBA

    My first post. I can usually find what I need and don't need to post but I've come up empty on this one. I am exporting several tables from Access to Excel 2013 with VBA and then formatting the sheets as tables, adding NumberFormats and so on. My titles are abbreviated so I want to add verbose...
  15. A

    'Object Required' error in macro for finding duplicate using ListObjects

    Hi, I am trying to write a macro which highlights the second, and all following duplicates, in a table using ListObjects Unfortunately, it keeps returning an 'Object Required' error when arriving at the "countif" function. In the debug mode, the variable "Dupl_Cell" has a value when I hover...
  16. A

    Strange Listobject behaviour

    Hi all, hope some one can help. Recently I have begun working with ListObjects, handy things definitely but they seem temperamental. In my current project i have a handful of them to store lists of data, my code seems to manage them well adding entries, removing entries etc but after around 6 or...
  17. M

    Retrieve Listobject Name w/o VBA

    Dear Excel Experts I may be asking a question for something that cannot be done. Nevertheless, I thought there is no harm asking. Is there a way to retrieve the name of the listobject/table which is located in, for example, cell A5 without the use of VBA? Thanks Maria
  18. R

    ListObjects and individual cell processing

    This is a bit of an open-ended question. I am using ListObjects quite a bit to render table-like data onto Excel using VBA. I basically pass a two dimensional array of type 'System.Object' from managed code (C#) and through automation, accept it as Variant on the VBA side through a macro. Even...
  19. M

    How to use a loop to clear all filters in all tables in a workbook?

    Windows 7, Excel 2010 I have workbook with 6 data tables fed by data connections. When I close the workbook, I want to clear all the filters in each of the tables before saving and closing the workbook. I have a lazy macro that loops through the each table and sheet, but doesn't clear the...
  20. M

    Highlight autofiltered column headers

    Good evening - I searched this forum and did not find the answer to my question and was wondering if someone could provide help? I am querying a SQL Server table to return query results into Excel 2010; it is returning 55+ columns. Because there are more than 20 columns being returned, I'd...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back