Copy excel range to another workbook with table and values

[Jbone697]

I have this macro that copies that values, but doesnt copy over the table.

Sub OpenReport()
Set NewBook = Workbooks.Add
  Workbooks("TMSRULES.xlsm").Worksheets("Sheet1").Range("A115:J178").Copy
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
 End Sub

If I remove (xlPasteValues) from the macro, it copies over the table without the values.

I have searched for hours on how to get this working correctly and nothing has seemed to work. Please help, thanks.

 

[My Aswer Is This]

Here is how I copy a Table:

Sub Test()
'Modified 6/21/2018 11:25 PM  EDT
Sheets(1).ListObjects("Table1").Range.Copy Sheets(2).Range("G3")

 

[Jbone697]

This doesnt work for me. It also looks like this isn't what im looking for even if I did get this to work.

 

[My Aswer Is This]

Maybe you could explain in words what your wanting to do. And maybe I can help you.
Sounds like you want to copy a Table.

Your subject says:

Copy excel range to another workbook with table and values​

 

[Jbone697]

Yeah, copy the table.... and pasting it to new workbook. My macro copies fine. It doesn't paste both the format and the vaules of the table. It pastes one or the other. I hope this better helps your understanding of my issue.

 

[My Aswer Is This]

Here is what I think you need:
Just modify the workbook name.
And you need to use the Table name
All Tables have a name.

See mine is named Table1

Sub Copy_Range()
Set NewBook = Workbooks.Add
Workbooks("Book1").Worksheets("Sheet1").ListObjects("Table1").Range.Copy NewBook.Sheets(1).Range("A1")
 End Sub

This worked for me.

 

[Jbone697]

Thanks for your help. Unforrtunately, this isnt working for me. It's giving me a runtime error 9 like a lot of what Ive tried.

 

[My Aswer Is This]

Show me the exact script you used.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...copy-excel-table-to-a-new-workbook-macro.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Jbone697]

I apologize, this has been resolved per that link. I don't know how to mark as resolved on this site.