Creating a List

creates a list around the active cell.

ActiveWorkbook.ListObjects.Add SourceType:=xlScrRange.xlSrcExternal, _ 
                      Range("A2:C5"), , xlNo 

When SourceType is xlScrExternal, the source argument is a two element array containing the following:
1) The sharepoint list address plus the folder name
2) The name (or GUID) of the list

It is a good idea to create a new workbook when inserting a shared list manually.

SharePoint Lists Web Service to access the list directly though code.