Checking certain conditions on a workbook

[JJS]

Hi,

I have a process that currently run weekly which pulls data from 12 individual workbooks into one master workbook for an executive view.

Each user of the individual workbooks has the ability to create a list range in their workbook to add new data using the

 ActiveSheet.ListObjects.Add(xlSrcRange, Range("list_range"), , xlYes).Name = _
        "List1"

method and an offset named range. This is done to extend data validation fields and formulas to new cells as data is added. I have this set up using a simple macro to start it and a macro to convert it back to a range again.

Problem is that users forget to run the second macro and worksheet stays in list mode making the consolidation process error out when it hits a workbook with this condition.

I am looking for a way to check each workbook when the consolidation macro runs to see if the list mode is still engaged and if so run the convert to range macro. I am unsure if this is possible and what the VBA code would look like for it.

Any help is appreciated, if you need further clarification please let me know

 

[Damon Ostrander]

Hi JJS,

Here is a function you can use at the beginning of the consolidation process code to determine if the table still exists--if not it has been converted back to a range.

Function TableExists(TableName As String, Optional SheetName As String = "") As Boolean
   On Error GoTo NoSuchTable
   Dim LO      As ListObject
   Dim WS      As Worksheet
   If SheetName = "" Then
      Set WS = ActiveSheet
   Else
      Set WS = Worksheets(SheetName)
   End If
   Set LO = ActiveSheet.ListObjects(TableName)
   TableExists = True
   Exit Function
NoSuchTable:
   TableExists = False
End Function

Simply place this code in a standard macro module in your workbook. You can then use it in your code to return the table to a simple range like this:

If Not TableExists("List1") Then ActiveSheet.ListObjects("List1").UnList

If the worksheet does not happen to be the active sheet, you can add the optional worksheet name to do it like this:

If Not TableExists("List1","Sheet2") Then Worksheets("Sheet2").ListObjects("List1").UnList

where Sheet2 is the worksheet containing the Table.

Keep Excelling.