Macro to copy all tables onto one sheet

[JudyF]

Hi,

I have an Excel file with multiple tables on multiple sheets. I want to be able to pivot all tables, so I want to have a macro I can run that will copy all data from the macros and "paste special" onto one sheet that I can pivot off of. I would like data to always be copied to the same sheet so the pivot can remain in tact, so I need the data to be cleared out each time the macro runs before copying the new data onto the sheet for pivot. Each sheet will have the name "Dept" in it. There will be many other sheets in the workbook so it should look for any sheets that have the name "dept" in the sheet name. The titles are identical, and should only be copied in once for the top row. Any help with writing the macro would be greatly appreciated. I am a beginner at VBA. Thanks!

[CK76]

This should get you started. See attached. I've added "Data" sheet where tables are consolidated.

Please Login or Register  to view this content.

[JudyF]

I think this will work - thank you!!!! I'm trying to understand what this is doing... How will it know what range of data to pick up from each sheet? I so appreciate your help!

[CK76]

Please Login or Register  to view this content.

This part clears everything except Row 1 (by using Offset) from Data sheet.

Please Login or Register  to view this content.

Loops through each worksheet in Worksheet collection that belongs to the workbook

Please Login or Register  to view this content.

Checks if "dept" is contained in the worksheet name

Please Login or Register  to view this content.

If cell A1 of Data sheet is empty, then copy HeaderRowRange of Table (i.e. ListObject) and paste.

Since there is only one table per sheet, the table you want to copy will always be ListObjects(1).

Please Login or Register  to view this content.

Copies entire data range of table (excluding header) and paste to last used row + 1 in Data sheet.

[JudyF]

Thanks for the explanation!
If there are other named ranges on a sheet with a table, is that OK? What if there were another table on a sheet? Is there a way I could designate which to select? The one I want will always start in A8.

[JudyF]

Instead of saying that the word "dept" would be in the name of each tab it should look at, can you just tell it that if the tab names begins with a number it should look at it. Otherwise, skip over it?

[JudyF]

Hi, hoping you're still there and can help me with the last few questions. Also, the data I'm copying has formulas (the sample I sent you didn't have any). I need to do a "paste special" when pasting to the data sheet so the formulas don't follow along and error out. You've been so helpful. Please let me know if you can answer the last few questions. Thank you again!

[CK76]

I've been in a meeting for last 3 hours Modified code to check that sheet name has number string and to copy values only.
I assumed Dept# is always 3 characters or more. If different, let me know.

The code is meant for Windows machine.

Please Login or Register  to view this content.

[JudyF]

I hope I didn't disturb you and thank you for taking time out of your day and helping me.
The department number is always 3 digits. If there is a 2 digit, or a 4 digit number also existed in the name, for example "210 test 90200A", would it know to ignore the string longer than 3 digits? Or would it ignore something that was like 210test all run together? I am realizing depending on your answers that the criteria might be a tough one.
Also, if there are other named ranges or other tables on the sheet, will that interfere? It would be safe to tell it to always take the data from the table including/starting with cell A8.
Again, thank you so much!

[CK76]

would it know to ignore the string longer than 3 digits?

Yes.

Or would it ignore something that was like 210test all run together?

It would ignore this as well.

Does department number always belong at start of sheet name? If so, change .Pattern to...

Please Login or Register  to view this content.

Also, if there are other named ranges or other tables on the sheet, will that interfere

Named ranges would not matter. Other tables may (if their index is 1), this is dependent on order in which tables were created on the sheet.

I'm heading out for the night. I'll check tomorrow.

[JudyF]

Thanks! Looks like I'm having an issue with re-running the macro and getting the new data to start in A2, and have the sheet be formatted as a table. Looking forward to hearing back tomorrow. Again, I really appreciate your help!

[JudyF]

Hi CK76,
Just hoping you'd have time to take another look at the macro for me....

[CK76]

Try this one.

Please Login or Register  to view this content.

[JudyF]

I have copied your macro into my file. The file I sent you is slightly different from mine in order to not send actual data. I thought I hadn't changed anything that would affect the macro, but apparently I did.
I'm getting a Run-time error '91': Object variable or with block variable not set, and when I debug, the following line is highlighted in yellow: ws.Range("A8").ListObject.DataBodyRange.Copy. It copies the first department, but then dies and doesn't do the others. Right now, I have three, but it dies after the first. I realize it's hard to trouble shoot without seeing the actual file, but does the error help any? Thank you!!!!!

[CK76]

That likely means that you don't have table intersecting cell A8 on some of the sheets.

I recommend that you set up all tables (that you want merged) to start at same cell. And change cell reference to that cell.

[JudyF]

So, I have a lot of other tabs that should not interfere with the macro, but something apparently is. I deleted all other tabs, and the macro ran. I have a list of all the tabs. Do you think any of these tab names would interfere make the macro think it should go to any of those tabs rather than skip them? Or can you think of something else I would have on another sheet that could cause that error? The 4 tabs I'm using are the ones followed by "**" (Data, 204 Art Sci Computing, 210 IT Admin and 212 Media Services). Also , any chance you'd have time to outline for me what each line does? I'm still very much a beginner at VBA and am trying to learn as much as I can. You have been a huge help!
FY19 Bud Ref
Data**
FY18 Op Accts
Compensation
BUOBs & Categories
Department Info
Tables (2)
204 Art Sci Computing**
204fy17 Art Sci Comp
210 IT Admin**
210old IT Ad
212 Media Services**
212fy17 Media Svcs
214old End User Svcs
217old EA 90200A
217old EA A&D 90217D
217old EA BIDW 90217E
217old EA UA 90217M
280old OCIO
282old CP
283old CISO
284old PPMO
285old SOC
286old MS Network Eng
286old WURN 90200W
286Data Centers 90201
287DNF Network Eng
288Platform Eng
289Enterprise Eng
291Dept Apps
324STS
325STS
336Soft Lic
367Telecomm
Expense Credit BUOB's
Pivot Bal check tracking revenu
Tracking Revenues

[CK76]

These.

204 Art Sci Computing**
204fy17 Art Sci Comp
210 IT Admin**
210old IT Ad
212 Media Services**
212fy17 Media Svcs
214old End User Svcs
217old EA 90200A
217old EA A&D 90217D
217old EA BIDW 90217E
217old EA UA 90217M
280old OCIO
282old CP
283old CISO
284old PPMO
285old SOC
286old MS Network Eng
286old WURN 90200W
286Data Centers 90201
287DNF Network Eng
288Platform Eng
289Enterprise Eng
291Dept Apps
324STS
325STS
336Soft Lic
367Telecomm

Any sheet starting with 3 numbers in sheet name would impact the code. Let me think on logic.

[JudyF]

I can change the name so that only the ones that I want will start with the 3 numbers. The others can have the 3 numbers at the end. Would that work?

[CK76]

Yes. That would resolve the issue.

[JudyF]

That worked! Fantastic!!!!!!
If you have time to identify what each line does, that would be great. If not, I completely understand. Again, you've been a huge help. This is the first time I've used the forum. Is there something I can do to recognize your help???

[MarvinP]

Hi Judy,

There is a newer tool in Excel called Power Query in 2010 and 2013 Excel and now called Get & Transform in 2016. It can append all your tables without using any VBA. Let me describe how I appended your tables using this free Add-In from Microsoft that you can download and install.

1. I opened your sample file on post 1 above
2. Click on 101 Dept test1 Tab and click anywhere in the table
3. Click on the Data Tab (might be your PQ tab) and "From Table/Range"
4. This brings up the PQ window with your data loaded in it.
5. Click on Close & Load dropdown and then on "Close and Load To"
6. Select "Only Create Connection" and then OK.
7. Do the above steps for tables on 102 and 103.
8. You will now have 3 tables with connection only in your PQ buffer
9. Now find the "Combine Queries Icon" and click on Append.
10. In the Append Dialog click on "Three or more tables"
11. Add all your tables to the box on the right and click OK
12. Click on the "Close & Load" Icon but this time to a New Sheet.

This will create a new sheet in your workbook with all your tables appended into a single table, ready to pivot.

No VBA, No Formulas, just mouse clicks (about 20 in total) to append all these tables into a single table.

[CK76]

@MarvinP

Yes, PowerQuery is tool of choice for merging data.
However, for Excel 2013, you'd need specific license SKU to install PowerQuery Add-in (ProPlus, E3, Excel Stand-alone etc).
Most standard SKU would lack this option. It was my biggest gripe when I was using Office 365 (when it was still 2013 version) for business.
I've since upgraded my subscription to ProPlus and loving it

[CK76]

@JudyF

See if this helps you. For legibility, paste it into your module

Please Login or Register  to view this content.

See link for pretty good tutorial on how to use Regular Expression in VBA.
http://analystcave.com/excel-regex-tutorial/

Is there something I can do to recognize your help???

You've already given me rep
Just mark the thread as [Solved] by using Thread tool found at top of your first post.

[MarvinP]

For Judy,

Here are the details on Power Query Add-In for 2013.

https://www.microsoft.com/en-us/down...d-d5e0aa2c0c05

In the System Requirements it claims it works with Excel 2013 Stand Alone..

The following Office versions are supported:
Microsoft Office 2010 Professional Plus with Software Assurance
Microsoft Office 2013:
Power Query Premium: All Power Query features available for: Professional Plus, Office 365 ProPlus or Excel 2013 Standalone

[CK76]

That document is misleading.

PowerQuery/PowerPivot is available for all license SKU for 2010 (I used to use it on my Small business edition until I got ProPlus).

But correct for 2013 (though it doesn't list Enterprise licenses E3/E4 has this feature available).

In the System Requirements it claims it works with Excel 2013 Stand Alone..

I can confirm this is true. I have Stand-alone Excel at home and have PowerQuery and PowerPivot installed.

[JudyF]

Hi,
A few weeks ago you wrote a macro for me to copy the table from all sheets with sheet names that starts with 3 numbers onto one tab. It has been wonderful!!! I was wondering if you could help me amend it slightly. I have attached a new sample here, as I've changed the format a little. I was wondering if you would be able to tell the macro that if the amount in column J is equal to 0, then skip that line. I'd also like to have column J on the data tab formatted to accounting, no symbol and no decimals.
The macro I'm using is currently:

Sub Copy_All_For_Pivot()
Dim tbl As ListObject
Dim ws As Worksheet
Sheets("Data").Cells(1).CurrentRegion.Clear
For Each ws In ThisWorkbook.Worksheets
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "^[0-9]{3}"
If .Test(ws.Name) Then
If Sheets("Data").Range("A1") = "" Then
ws.Range("A8").ListObject.HeaderRowRange.Copy
Sheets("Data").Range("A1").PasteSpecial xlValues
End If
ws.Range("A8").ListObject.DataBodyRange.Copy
Sheets("Data").Range("A" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial xlValues
End If
End With
Next
With Sheets("Data")
.ListObjects.Add(xlSrcRange, .Cells(1).CurrentRegion, , xlYes).Name = "DataTable"
.ListObjects("DataTable").TableStyle = "TableStyleMedium2"
End With
Range("A1").Select
Sheets("FY18 Bud Ref").Select
Range("C13").Select
ActiveSheet.PivotTables("FY19 Budget Reference Pivot").PivotCache.Refresh
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CutCopyMode = False
End Sub










' Dim tbl As ListObject
' Dim ws As Worksheet
''Clears everything except Row 1 (by usingOffset) from Data Sheet
' Sheets("Data").Cells(1).CurrentRegion.Offset(1).Clear
'
''Loops through each worksheet in Worksheet collection that belongs to the workbook
' For Each ws In ThisWorkbook.Worksheets
'
''Checks if "dept" is contained in the worksheet name
' If InStr(1, ws.Name, "dept", vbTextCompare) Then
'
''If cell A1 of Data sheet is empty, then copy HeaderRowRange of Table (i.e. ListObject) and paste.
''Since there is only one table per sheet, the table you want to copy will always be ListObjects(1).
' If Sheets("Data").Range("A1") = "" Then ws.ListObjects(1).HeaderRowRange.Copy Sheets("Data").Range("A1")
'
''Copies entire Data range of table (excluding header) and paste to last used row + 1 in Data sheet
' ws.ListObjects(1).DataBodyRange.Copy _
' Sheets("Data").Range("A" & Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1)
' End If
'Next
'
'End Sub