1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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
  16. 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...
  17. 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...
  18. 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...
  19. L

    Help Deleting List Objects List Columns

    Hi, I have some code that will go through a table and delete the fields that I have deselected on a different tab. The part where I am having difficulty is the ListObject.ListColumns(#).Delete part. The problem is that the # changes, but I haven't put it in my code correctly to dynamically...
  20. S

    ListObjects.Add ADO

    I've tried to import data with following code With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("DSN=db_name; UID=user_name; PWD=pw;"), Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM table") End With...

Some videos you may like

This Week's Hot Topics