Okay, I created 3 subroutines, clearTable clears the table on the sheet, copyExport copies the data from the last sheet which has data from an export on it, and createTable creates a new table for the updated data.
I use these for all 6 sheets in my workbook that have tables. All the tables are based on the same information from the export sheet but they have data filtered differently.
These 3 function calls are the beginning of the macro, the rest of it just formats the tables and runs the filters per the requirements of the different sheets.
The trouble is, the macro runs the sub perfect until createTable for sheet 2 then I get an error saying: "The worksheet range for the table data must be on the same sheet as the table being created."
Well, it IS! And it works just fine for Sheet 1!
So I don't get what's wrong!
Here's how I call the subs:
Code: Select all
Private Sub CommandButton1_Click()
ClearTable "Sheet 1", "Sheet 1_Table"
ClearTable "Sheet 2", "Sheet 2_Table "
ClearTable "Sheet 3", "Sheet 3_Table "
ClearTable "Sheet 4", "Sheet 4_Table"
ClearTable "Sheet 5 ", "Sheet 5_Table "
ClearTable "Sheet 6", "Sheet 6_Table "
copyExport "Sheet 7", "Sheet 1"
copyExport "Sheet 7", "Sheet 2"
copyExport "Sheet 7", "Sheet 3"
copyExport "Sheet 7", "Sheet 4"
copyExport "Sheet 7", "Sheet 5"
copyExport "Sheet 7", "Sheet 6"
createTable "Sheet 1", "Sheet 1_Table", "Sheet 1_Table[#All]", "TableStyleMedium11"
createTable "Sheet 2", "Sheet 2_Table ", "Sheet 2_Table [#All]", "TableStyleMedium10"
createTable "Sheet 3", "Sheet 3_Table ", "Sheet 3_Table [#All]", "TableStyleMedium10"
createTable "Sheet 4", "Sheet 4_Table", "Sheet 4_Table[#All]", "TableStyleMedium15"
createTable "Sheet 5", "Sheet 5_Table ", "Sheet 5_Table [#All]", "TableStyleMedium9"
createTable "Sheet 5", "Sheet 6", "Sheet 6_Table [#All]", "TableStyleMedium9"
AND Here's the subs:
Code: Select all
'Pass table name and sheet name to clear the existing table
Sub clearTable(sName As String, tName As String)
With Sheets(sName)
.Activate
.ListObjects(tName).Unlist
.Cells.Clear
Cells.EntireRow.AutoFit
End With
End Sub
'Pass export sheet name and sheets name for pasting into
Sub copyExport(exName As String, shName As String)
Sheets(exName).Activate
ActiveSheet.Range("A1").CurrentRegion.Select
Selection.SpecialCells (xlCellTypeLastCell)
Selection.Copy
Sheets(shName).Select
ActiveSheet.Range("A4").Select
ActiveSheet.Paste
End Sub
'Pass sheet name, table name, table range for naming and table style for new table
Sub createTable(shtName As String, tName As String, tRangeName As String, tStyle As String)
Sheets(shtName).Activate
ActiveSheet.Range("A4").Select
ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A4").CurrentRegion, , xlYes).Name _
= (tName)
Range(tRangeName).Select
ActiveSheet.ListObjects(tName).TableStyle = (tStyle)
End Sub