Delete existing, Copy/Paste data , Create new table in VBA

[Asher]

This is driving me nuts because it works in one sheet but not another! Ugh!

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

Ca someone tell me why the createTable stops and gives me that error at sheet 2?

[Asher]

excuse the typo for sheet 6, it's

Code: Select all

createTable "Sheet 6", "Sheet 6", "Sheet 6_Table [#All]", "TableStyleMedium9"

[HansV]

Your naming is inconsistent: sometimes you use "Sheet 5", at other times "Sheet 5". Similarly, you have "Sheet 6", and "Sheet 6_Table".

In general, it isn't necessary to select sheets and ranges to do something with them; code runs more efficiently if you don't.

Here is a streamlined version of your code with more consistent naming:

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 6", "Sheet 6_Table", "Sheet 6_Table [#All]", "TableStyleMedium9"
End Sub

'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).Range("A1").CurrentRegion.Copy _
        Destination:=Sheets(shName).Range("A4")
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)
    With Sheets(shtName)
        With .ListObjects.Add(xlSrcRange, .Range("A4").CurrentRegion, , xlYes)
            .Name = tName
            .TableStyle = tStyle
        End With
    End With
End Sub

[Asher]

Hans,

Can you repost that code, it isn't all showing up in my view on the forum like it did in the email, and the code in the email has all those &# and whatsajiggits.

The naming I used to post my code isn't the real naming because I'm trying to keep things I post as generic as possible.

Thanks for pointing out the inconsistancies though.

[Asher]

Oh never mind reposting it, I just hit the reply "Quote" button and it showed me all the code. I wonder why it doesn't show on my view in your reply, like, only the first few lines show...

[HansV]

You can easily copy all code from my previous reply as follows:

- Click the SELECT ALL link next to the word CODE:

x240.png

- Press Ctrl+C to copy the code to the clipboard, ready to be pasted into a VBA module.

[HansV]

Don't you see the vertical scrollbar on the right hand side of the CODE box?

x241.png

[Goshute]

Click the SELECT ALL link next to the word CODE

Hans, the SELECT ALL link may not appear in all skins - it doesn't show for me.

(Asher - 'selecting all' is quite easy without a Select All button: select the first line of code, hold shift key and use the down arrow key to extend the selection to the last line in the code box, use the right arrow key to extend the selection to the end of the last line if necessary.)

[HansV]

You're correct - SELECT ALL was added in the default prosilver skin (and adopted in the AeroBlack skin); it's not in subsilver2 which is a relict from the previous version of the forum software, and in skins based on it such as Rainbow Pearl.

[Asher]

I was in Rainbow Pearl. Now I've switched back and I can se it all. Thanks all.
Thoise changes worked great Hans, thanks so much!!!