Excel 2010 macro: copy a table from one workbook to another 1

[RBLampert]

I'm building a macro that will copy a table from one workbook to another. My problem is that when the code reaches the point where it's to switch back to the target workbook, I get a run-time error 424: "Object required." WHAT object?

Here's the code:

Sub Copy_In_Module_Totals()
'
' CopyInModuleTotals Macro
' This macro brings in the Range of information that will be the reference for the later VLOOKUP functions.
'

' Select the upper left cell of the range the table will be copied into.
Range("N1").Select

' Open the file that contains the table, select it, and copy it.
Workbooks.Open Filename:= _
"C:\Users\Owner\Documents\Air Force Association\Field Council\Training Subcommittee\Lookup Table Source.xlsx"
With ActiveSheet
.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = _
"ModTotalTimeSlides"
End With
Range("ModTotalTimeSlides[#All]").Select
Selection.Copy

' Switch to the other workbook. The intent is to go back to the already-open workbook, irrespective of which file it is
'(it will change every month).

[highlight #FCE94F] ActivateWindow.ActivateNext

' It's at this point, when trying to execute the step above, that I get this error: Run-time error '424': Object required. WHAT object?[/highlight]

' Paste the table into the other workbook.
ActiveSheet.Paste
Selection.Columns.AutoFit
Application.CutCopyMode = False
Range("A1").Select

End Sub

I saw Skip Vought's advice in thread 707-629699 that suggested using the Copy-Destination method but it appears to assume that the destination workbook's filename will be the same every time. That's a problem: mine will be DIFFERENT every time! Everything else in the code above works. It's just this one stinkin' detail that's holding me up.

Suggestions?

 

[SkipVought]

hi,

Sub Copy_In_Module_Totals()
'
' CopyInModuleTotals Macro
' This macro brings in the Range of information that will be the reference for the later VLOOKUP functions.
'

    With ActiveSheet        '[b]this is the sheet that you will copy into later[/b]
    
        
        ' Open the file that contains the table, select it, and copy it.
        With Workbooks.Open(Filename:= _
            "C:\Users\Owner\Documents\Air Force Association\Field Council\Training Subcommittee\Lookup Table Source.xlsx")
            With .ActiveSheet    '[b]ActiveSheet HERE is dangerous IF this sheet was not active when the workbook was last saved![/b]
                .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = _
                "ModTotalTimeSlides"
                .Range("ModTotalTimeSlides[#All]").Copy
            End With
        End With
        
        ' Switch to the other workbook. The intent is to go back to the already-open workbook, irrespective of which file it is
        '(it will change every month).
        
        .Activate       '[b]activate the original workbook's active sheet[/b]
    
        ' It's at this point, when trying to execute the step above, that I get this error: Run-time error '424': Object required. WHAT object?
        
        ' Paste the table into the original workbook.
        Range("N1").PasteSpecial xlPasteAll
        .UsedRange.Columns.AutoFit
        Application.CutCopyMode = False
        .Range("A1").Select
    End With
End Sub

 

[RBLampert]

Thanks, Skip! Works like a champ--just as I expected it would. I don't think the ActiveSheet command will be a problem as both the source and destination workbooks will have only one worksheet in them.