Copy external worksheets to current workbook, Excel vba 1

[BxWill]

Goal is to copy two worksheets from two separate closed workbooks (source workbooks) - named CepAM_201309" and "CepAN_201209" that are located at C:\Test\ to the active workbook at C:\Review\ that is titled CashRev.xlsm

Each of the source workbooks have three worksheets - "Summary", "Detail" and "Miscellaneous." I am only interested in copying the active range from the worksheet titled "Detail" within each respective source workbook to the respective worksheets - "CepAM_Current" and "CepAM_Prior" within the active workbook.

Using the code below, I am able to copy from the first source workbook - CepAM_201309 but not able to copy the data from the second
source workbook - CepAM_201209.

What modifications to the code below will allow me to copy the data from the two source workbooks to the respective worksheets within the active workbook?

Also, is there a method to include a field in the copied data that indicates the date and time that the data was loaded into the worksheets within the active workbook?

Thanks in advance for any insight.

Sub mcrImportCemAN_CurrMonthCurrYear()
'
' mcrImportCemAN_CurrMonthCurrYear Macro
Dim wb As Workbook
Dim activeWB As Workbook
Dim FilePath As String
Set activeWB = Application.ActiveWorkbook
FilePath = "C:\Test\CepAM_201309_Excel.xlsx"  'option 2 is to allow me to select file
Set wb = Application.Workbooks.Open(FilePath)
wb.Worksheets("Detail").Copy
Sheets("Detail").Select
Range("A1:O38827").Select 'Need to select active range
Selection.Copy
Windows("CashRev.xlsm").Activate
Sheets("CepAM_201309_Excel").Select
ActiveSheet.Paste
Windows("CepAM_201309_Excel.xlsx").Activate
ActiveWorkbook.Close
FilePath = "C:\Test\CepAM_201209_Excel.xlsx"  'option 2 is to allow me to select file
Set wb = Application.Workbook.Open(FilePath)  'Code stops here
wb.Worksheets("Detail").Copy
Sheets("Detail").Select
Range("A1:O38827").Select  'Need to select active range
Selection.Copy
Windows("CashRev.xlsm").Activate
Sheets("CepAM_201209_Excel").Select
ActiveSheet.Paste
Windows("CepAM_201209_Excel.xlsx").Activate
ActiveWorkbook.Close
End Sub

 

[SkipVought]

Hi,

Plural workbooks.open on that statement.

BTW, please post VBA questions in forun707 for better results.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]