copy column data from table in another workbook

[LlebKcir]

well scratching head on this one. down to the worthless error message Run-time error '13' Type mismatch. no indication were in the code the mismatch takes place.

Sub ColumnNames()

Dim i As Long
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim table As ListObject
Dim tblCols As ListColumns
Dim tblCol As ListColumn
Const MasterPath As String = "z:\path\to\file\Master.Spreadsheet-13-Feb-2019-2.xlsm"
Dim mtable As ListObject
Dim mtblCols As ListColumns
Dim mtblCol As ListColumn

Set wb1 = ThisWorkbook.Worksheets
Set wb2 = Application.Workbooks(MasterPath)
Set ws2 = wb2.Sheets("City")
Set mtable = ws2.ListObjects("tCity")
'Set mtable = Range("tCity").ListObject
Set mtblCols = mtable.ListColumns


Set ws = Worksheets("Overview2")
'Set table = ws.ListObjects("tOverview")
Set table = Range("tOverview").ListObject
Set tblCols = table.ListColumns

    For Each tblCol In tblCols
        'MsgBox tblCol.NAME
        mtblCols(tblCol).DataBodyRange.Value = table.ListColumns(tblCol).DataBodyRange.Value
    Next tblCol
        
End Sub

Running the code from Comm spreadsheet (ThisWorkbook)

In the Comm workbook I have 41 columns that have identical header names as the table on the Master. The tables in the Master have over 100 columns, but I only need the 41 for now. Goal for this step is to have both workbooks open at the same time. identify the column header names in tOverview on Comm workbook. Look at Master workbook tCity table find same column headers and copy column data from Master to Comm.

For now I am OK with the tCity path being hard coded. Down the road that will need to be variable, but that can wait until I can get this code working :D.

Thank you in advance.

 

[Fluff]

This line is wrong

Set wb1 = ThisWorkbook.Worksheets

Remove the part in blue.

Also, when referring to workbooks, you should only use the workbook name, not the full path & name, so I would expect this line to fail

Set wb2 = Application.Workbooks(MasterPath)

 

[LlebKcir]

Fluff, thank you for the feedback. I will get a chance to try the changes soon. The reason for the full path was for preparation to having to access that location later from a SharePoint location.

Do I not need the full path except to open that file? Once the file is open I can just set a variable to foo = Master.xlsm?

 

[Fluff]

That's right, you need the full path to open the file, but only the filename in

Set wb2=Workbooks("Master.xlsm")

 

[LlebKcir]

Sub ColumnNames()

Dim i As Long
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim table As ListObject
Dim tblCols As ListColumns
Dim tblCol As ListColumn
'Const MasterPath As String = "z:\path\to\file\Master.Spreadsheet-13-Feb-2019-2.xlsm"
Dim mtable As ListObject
Dim mtblCols As ListColumns
Dim mtblCol As ListColumn

Set wb1 = ThisWorkbook
Set wb2 = Workbooks("Master.Spreadsheet-13-Feb-2019-2.xlsm")
Set ws2 = wb2.Sheets("City")
Set mtable = ws2.ListObjects("tCity")
'Set mtable = Range("tCity").ListObject
Set mtblCols = mtable.ListColumns


Set ws = wb1.Worksheets("Overview2")
'Set table = ws.ListObjects("tOverview")
Set table = Range("tOverview").ListObject
Set tblCols = table.ListColumns

    For Each tblCol In tblCols
        'MsgBox tblCol.NAME
        mtblCols(tblCol).DataBodyRange.Value = table.ListColumns(tblCol).DataBodyRange.Value
    Next tblCol
        
End Sub

Now receiving out of bound error.

Do I need to use tblCol.Name.DataBodyRange.Vaule instead?

 

[Fluff]

I very rarely deal with tables, so cannot help with that I'm afraid.

 

[LlebKcir]

ok thanks. the tblCol.Name... failed. so not to figure out howto grab the table header name from tOverview from Comm workbook and use those as the table headers in, this case tCity, from the Master workbook to copy/paste or .value = .value the data around.