Not understanding Error for ExT.Activate

Mr.GfC · Mar 29th 2016

I copied the Command button from another spreadsheet into this spreadsheet and changed the destinations, as well as the page names to reflect the new workbook. However, I'm getting an error on the ExT.Activate line, when I try to use the macro button. Not sure why. It works perfectly on the other spreadsheet and should be doing the same on this spreadsheet.

I did post same question here, but I couldn't upload the spreadsheet for some reason. The only thing I'd like help adding is clearing the data in the first table, columns A & B, with the same button.

Any help is appreciated.
Regards,
GfCs

philb1 · Mar 29th 2016

Re: Not understanding Error for ExT.Activate

Try changing ExT to Dt on every line that has it. You will also have the same issue with MEx

Mr.GfC · Mar 29th 2016

Re: Not understanding Error for ExT.Activate

Tried that, now it gives an error on the next line "lr = ExT.Cells(Rows.Count, 2).End(xlUp).Row + 1"

philb1 · Mar 30th 2016

Re: Not understanding Error for ExT.Activate

Try this

Code

Option Explicit


Private Sub CommandButton1_Click()
Dim Db As Worksheet, Dt As Worksheet
Dim lr As Long, x As Long
Dim Tbl As ListObject
Dim TableName As String
Set Db = Sheets("Daily Bidding")
Set Dt = Sheets("Daily Tracking")
TableName = "Table2" ' Call it what you like here
x = 0 ' Make sure it's zeroed before we start
lr = Dt.Cells(Rows.Count, 2).End(xlUp).Row + 1


Dt.Range("A" & lr).Value = Db.Range("E1").Value 'Date
Dt.Range("B" & lr).Value = Db.Range("L7").Value '# of Bets
Dt.Range("C" & lr).Value = Db.Range("M7").Value '# of Calls
Dt.Range("D" & lr).Value = Db.Range("N7").Value '# of Puts
Dt.Range("E" & lr).Value = Db.Range("I8").Value 'Win %
Dt.Range("F" & lr).Value = Db.Range("I9").Value 'Loss %
Dt.Range("G" & lr).Value = Db.Range("I10").Value 'Draw %
Dt.Range("H" & lr).Value = Db.Range("F10").Value 'Made
Dt.Range("I" & lr).Value = Db.Range("I12").Value 'Lost


'   You can't place a table on top of a table so we
'   test if the table already exists on Sheet2
For Each Tbl In Dt.ListObjects
    If Tbl.Name = TableName Then x = x + 1
Next Tbl


'   If the table doesn't exist then create it
If x = 0 Then
    Dt.ListObjects.Add(xlSrcRange, Dt.Range(Dt.Cells(1, 1), Dt.Cells(lr, 9)), xlYes).Name = TableName
Else: End If


Exit Sub
End Sub

Display More

Mr.GfC · Mar 30th 2016

{RESOLVED} Re: Not understanding Error for ExT.Activate

This is the code that I was able to get to work:

HTML

Private Sub CommandButton1_Click()
    Dim Db As Worksheet, Dt As Worksheet    Dim lr As Long
        Set Db = Sheets("Daily Bidding")        Set Dt = Sheets("Daily Tracking")
        Dt.Activate        lr = Dt.Cells(Rows.Count, 2).End(xlUp).Row + 1
        Dt.Range("A" & lr).Value = Db.Range("E1").Value 'Date        
        Dt.Range("B" & lr).Value = Db.Range("L7").Value '# of Bets        
        Dt.Range("C" & lr).Value = Db.Range("M7").Value '# of Calls        
        Dt.Range("D" & lr).Value = Db.Range("N7").Value '# of Puts        
        Dt.Range("E" & lr).Value = Db.Range("I8").Value 'Win %        
        Dt.Range("F" & lr).Value = Db.Range("I9").Value 'Loss %
        Dt.Range("G" & lr).Value = Db.Range("I10").Value 'Draw %
        Dt.Range("H" & lr).Value = Db.Range("F10").Value 'Made
        Dt.Range("I" & lr).Value = Db.Range("I12").Value 'Lost  
              
On Error Resume Next    Dt.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table2"
End Sub      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Display More

What I'm trying to figure out now, is how to get the same button to clear the data (only) in table 1, columns A & B from row #2-down the entire column, on the Db worksheet.
I've searched the web, but most the examples are for deleting rows or inserting them. The few that do talk about ClearContent, I don't understand. It transfers the data from cells on Db to "Table2" on Ct. But as stated, I can't find the correct way to get it to select columns A & B (minus the 1st row) and clear the data without deleting the rows on "Table1" on the Db sheet, which also deletes the cells that it's transferring the results from.
I've attached the working spreadsheet to this post.
:angrypc:

Thanks in advance for any help.
GfC

philb1 · Mar 30th 2016

Re: Not understanding Error for ExT.Activate

The code you're using forces the macro to override the error, I thought I'd work around using error control, but it's the same difference in the end
The code below is the easy way to delete the contents of columns A & B on the Daily Bidding sheet

Code

Private Sub CommandButton1_Click()


    Dim Db As Worksheet, Dt As Worksheet
    Dim lr As Long


        Set Db = Sheets("Daily Bidding")
        Set Dt = Sheets("Daily Tracking")


        Dt.Activate
        lr = Dt.Cells(Rows.Count, 2).End(xlUp).Row + 1


        Dt.Range("A" & lr).Value = Db.Range("E1").Value 'Date
        Dt.Range("B" & lr).Value = Db.Range("L7").Value '# of Bets
        Dt.Range("C" & lr).Value = Db.Range("M7").Value '# of Calls
        Dt.Range("D" & lr).Value = Db.Range("N7").Value '# of Puts
        Dt.Range("E" & lr).Value = Db.Range("I8").Value 'Win %
        Dt.Range("F" & lr).Value = Db.Range("I9").Value 'Loss %
        Dt.Range("G" & lr).Value = Db.Range("I10").Value 'Draw %
        Dt.Range("H" & lr).Value = Db.Range("F10").Value 'Made
        Dt.Range("I" & lr).Value = Db.Range("I12").Value 'Lost
        
        


On Error Resume Next
    Dt.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table2"


Db.Range(Db.Cells(2, 1), Db.Cells(Rows.Count, 1).End(xlUp)).ClearContents
Db.Range(Db.Cells(2, 2), Db.Cells(Rows.Count, 2).End(xlUp)).ClearContents


End Sub

Display More

Not understanding Error for ExT.Activate

Participate now!

Share