Excel vba - Import data from two worksheets into active workbook 1

[BxWill]

Using the following code to import two separate worksheets from two different workbooks into the active workbook. The code does indeed import the data but there are some efficiencies needed.

Specifically, I would like to modify the code to accomplish;

1. Close any unnecessary objects.
Currently, the code results in two workbooks - Book1 and Book2 that remain open.

2. Import the active range of data from the two worksheets within the two different workbooks. Currently, I specify the range of data to copy - A1:O38827 - from each worksheet

3. Add a column at the right of the imported data that displays the date that I imported the data. Something along the lines of "DateImported" that will contain the date and time that I imported the data.

Currently attempting to code desired modifications but would appreciate any additional insight.

What modifications to the code will enable me to accomplish my objectives?

Sub mcrImportCepAP_CurrMonthCurrYear()
'
' mcrImportCepAP_CurrMonthCurrYear Macro
Dim wb As Workbook
Dim activeWB As Workbook
Dim FilePath As String
Set activeWB = Application.ActiveWorkbook
FilePath = "C:\Test\CepAP_201309_Excel.xlsx"  
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("CepAP_201309_Excel").Select
ActiveSheet.Paste
Windows("CepAP_201309_Excel.xlsx").Activate
ActiveWorkbook.Close
FilePath = "C:\Test\CepAP_201209_Excel.xlsx"  
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("CepAP_201209_Excel").Select
ActiveSheet.Paste
Windows("CepAP_201209_Excel.xlsx").Activate
ActiveWorkbook.Close
'Columns("A:A").EntireColumn.AutoFit
'activeWB.Activate
'wb.Close False
End Sub

 

[SkipVought]

hi,

Your aditional column to the right is a ONE TIME THING. Just add a heading manually ant then subsequent data will be appended accordingly.

This assumes that you want ALL the data in the Detail sheets. It is my observation that this is most often what is required. If not, the code can be simply changed accordingly,

Sub mcrImportCepAP_CurrMonthCurrYear()
'
    ' mcrImportCepAP_CurrMonthCurrYear Macro
    Dim wb As Workbook
    Dim FilePath As String
    Dim i As Integer, lRow As Long, rng As Range
    
'[b]Use ThisWorkbook which is the workbook running the code[/b]
'[s]   Set activeWB = Application.ActiveWorkbook[/s]
    
    With ThisWorkbook
        For i = 0 To 1
            Select Case i
                Case 0
                    FilePath = "C:\Test\CepAP_201309_Excel.xlsx"
                Case 1
                    FilePath = "C:\Test\CepAP_201209_Excel.xlsx"
            End Select
            
            Set wb = Application.Workbooks.Open(FilePath)
            wb.Worksheets("Detail").Range("A1").CurrentRegion.Copy
            
            With .ActiveSheet
                lRow = .[A1].CurrentRegion.Rows.Count + 1
                .Cells(lRow, "A").PasteSpecial xlPasteAll
            End With
            
            wb.Close
        Next
        
        With ActiveSheet.UsedRange
            Set rng = .Cells(1, .Columns.Count).End(xlDown).Offset(1)
            With rng
                .Cells(1, 1).Value = Date
                .Cells(1, 1).AutoFill _
                    Intersect(Range(rng, rng.End(xlDown)), ActiveSheet.UsedRange), _
                    xlFillCopy
            End With
        End With
    End With
    
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Thanks for the insight.

The modified code resulted in data populating the active worksheet.

However, the data should be imported to the respective worksheet within the active workbook.

For example, data on the "Detail" worksheet within the workbook at "C:\Test\CepAP_201309_Excel.xlsx" should populate the worksheet in the active workbook that is titled "CepAP_201309" and the data on the "Detail" worksheet within the workbook at C:\Test\CepAP_201209" should populate the worksheet in the active workbook that is titled "CepAP_201209."

Also, the date that the records were imported should populate the last column for all of the records that were imported.

 

[SkipVought]

Where did THAT come from?

You stated, "Using the following code to import two separate worksheets from two different workbooks [highlight]into the active workbook[/highlight]."

Where is C:\Test\CepAP_201209 specified?

Why don't you spend some time working on your new requirement and trying to use my code as a guide to figure out how it might be done? There are certainly some good clues in there for you to discover and capitalize upon! Take a look at the Select Case and the With Activesheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Thanks.

I thought I stated there were two separate worksheets to import into the active workbook. Within the active workbook, the data should be ported to the respective worksheets within the active workbook.

Sorry for the confusion.

Thank you for the insight and time.

 

[SkipVought]

Well did you figure out a method after you studied the code and gave it some thought?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}