Copy / Paste Macro on a Dynamic Table that Uses Slicers / Filters

Hi Experts,

I need a quick bit of VBA that copy and pastes (values and formatting) my dynamic table (connected  to SharePoint) to another worksheet after the user has completed using various slicers and filters on the connected table.

It would take me a while to create a dummy workbook, as you wont be able to connect to the SharePoint list; however my tables column header starting in A12 is "Name". The value for data starting in A13 will always start with always start with a "2". The Table's width is to Column T and can have as many rows as Filtering / slicing does to it. I currently have the "Totals Row" featured turned-on, on the table; however, I'd like if the macro just down selected to the last bit of available data incase the user were to turn-off the totals row.

If you need more information just let me know.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Just upload the workbook even if it does not connect to SharePoint it does not matter as what you want is to copy data from a table to a worksheet and the most important is the table and its location and to where you want the whole thing copied.

-PolakAuthor Commented:
Here you go. Right into Sheet1 works fine with me (again, values and formatting only).
So let me recap.

You want a button when clicked to copy your table to sheet1 starting A1 format and values right ?

here is the code for that and you will see it in the attached file.

Sub CopyTable()
Dim WS As Worksheet
Dim WS1 As Worksheet
Dim I As Long, MaxRow As Long, MaxCol As Long

Application.ScreenUpdating = False

Set WS = ActiveSheet
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
MaxCol = WS.Cells(12, WS.Columns.Count).End(xlToLeft).Column

Set WS1 = Sheets("Sheet1")

WS.Range("A12", WS.Cells(MaxRow, MaxCol)).Copy
WS1.Range("A1").PasteSpecial xlPasteColumnWidths
WS1.Range("A1").PasteSpecial xlPasteFormats
WS1.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Yes, that will do it! I can take it from here to adapt to my real workbook, Thanks so much!
Your welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.