copying a table between workbooks with VBA

[miconian]

I want to copy a table from one workbook to another. The copied table will then become the source data for a pivot table, so I would like it to stay a table, instead of just being pasted data.

When I try it with the macro recorder I get this:

Sub copyTable()


    Application.Goto Reference:="EstimatesData"
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste

End Sub

The problem is that this does not paste the headers of the table; just the data.

I can copy the table with the headers too like this:

Range("EstimatesData[[#Headers],[FirstColumn]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste

...but that strikes me as rather clumsy.

Surely there is a way to tell VBA to just copy the entire table, including the headers?

Or perhaps there is some higher-level functionality for just copying a table between two workbooks, and I'm just missing the big picture?

Thanks!

 

[gino59]

Try...

Range("EstimatesData[#All]").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste

Cheers,
Gino

 

[miconian]

Hm. That works. Thanks.

So how do I then rename the table?

I was trying to do something like this:

 ActiveWorkbook.Names.Add Name:="EstimatesData",Activeworksheet.Selection

But apparently those are the wrong arguments for Add Name. I'm having trouble figuring out the proper syntax.

I guess we could also assume that since this is a brand new workbook, the table must be Table1, and then rename it, but I'm not sure how to do that either.

thanks...

 

[gino59]

I think this will work.

ActiveWorkbook.Names.Add Name:="EstimatesData"
Activesheet.Paste
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "MyNew_tbl"

Cheers,
Gino

 

[miconian]

I'm having trouble putting this all together. What I'm trying is this, but it's not working:

Sub moveData()

 
Range("EstimatesData[#All]").Select
    Selection.Copy
    Workbooks.Add

ActiveWorkbook.Names.Add Name:="EstimatesData"
ActiveSheet.Paste
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "MyNew_tbl"

End Sub

I get an object error on the first line unless the EstimatesData table is already selected. Then, if it is, I get an error on the ActiveWorkbook line.

Sorry for being obtuse...

 

[gino59]

This works for me. I created a file called Book1.xlsm, set up a table named EstimatesData and added this code to a module.

Sub Macro2()
'
' Macro2 Macro
'
    Range("EstimatesData[#All]").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="C:\your dir\EstimatedData.xlsx"
End Sub

Executed the procedure from within Book1.xlsm (making sure the original table was not selected) and it created EstimatedData.xlsx with a table named "EstimatesData" and with the headers.

Not sure why it's not working for you. I'm on Excel 2010.

Gino

 

[miconian]

Doesn't work for me. I'm in 2007. Anyway, that's fine, I think I am going to take a different approach and just copy the worksheet from one workbook to the other.

 

[merlin777]

when you do that, does it preserve the table as a table?

Doesn't work for me. I'm in 2007. Anyway, that's fine, I think I am going to take a different approach and just copy the worksheet from one workbook to the other.

 

[Jasp30]

I am trying similar but cannot get the following to work (falling down at the command below the comment 'copy reference table. I have also asked additional questions with comments ****. Thanks.

Private Sub Macro_Update_Click()

'open reference file to copy table from
Range("A11").Select
Workbooks.Open Filename:= _
"P:\........file path..........\MASTER Register.xlsm"

Windows("MASTER Register.xlsm").Activate

'copy reference table
Range("Table_Register").Select
Selection.Copy

'Navigate back to source file
Windows("New Master.xlsm").Activate

'****How do I make this save original file name as macro needs to work from different files???

'first row of table
Range("A11").Select
ActiveSheet.Paste
' Range("Table_Register[#All]").Paste

Selection.Replace What:="'MASTER Register.xlsm'!Table_Register", _
Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False
Range("A11").Select


Windows("MASTER Register.xlsm").Activate
ActiveWindow.Close

'****Also wold like to b able to close the file without the save file prompt or retain clipboard info*****

' Sets cell with updated date (today)
Range("B8") = Date

End Sub