Macro to open a series of files and copy data out of them, where am I going wrong

[GribbletheMunchkin]

Hello all

I'm writing a macro to open every excel file in a designated folder in order and while open, copy out two columns from that file and paste them into the original workbook.

I've written the code below (pinching some bits from this forum). So far it is
1. opening the files correctly
2. copying and pasting the data
3. closing the files correctly.

The only problem is that the pasting always happens in the file that has just been opened, rather than the original file. I am using
<Original workbook>.Activate
Sheets("<correct worksgeet>").Activate
to try to move to the desired workbook and sheet before pasting but it isn't having it.

Can anyone spot where I am going wrong?

N.B. I am aware that there are more techy ways of doing this with less code, however, I am trying to use the simplest code to understand in this macro. I'm the only coder in my office, so if I leave my colleagues need to be able to puzzle out what I've been up to.




Sub AggregateGenerator()

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim MyPath As String
Dim ColumnPasteCounter As Integer
Dim OriginalWkrbk As Workbook


MyPath = ThisWorkbook.Path & "\Test\"
ColumnPasteCounter = 4

On Error Resume Next

OriginalWkrbk = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = MyPath
.FileType = msoFileTypeExcelWorkbooks



If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.


Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

OriginalWkrbk.Activate
Sheets("Aggregate Data").Activate
Columns(ColumnPasteCounter).ColumnWidth = 5
wbResults.Activate
Columns(2).Copy
OriginalWkrbk.Activate
Sheets("Aggregate Data").Activate
Columns(ColumnPasteCounter + 1).Paste
wbResults.Activate
Columns(3).Copy
OriginalWkrbk.Activate
Sheets("Aggregate Data").Activate
Columns(ColumnPasteCounter + 2).Paste

ColumnPasteCounter = ColumnPasteCounter + 3


wbResults.Close SaveChanges:=True

Next lCount
End If
End With

On Error GoTo 0


End Sub

 

[JackDanIce]

You should try to avoid using .Activate and .Select as much as possible. Most Excel objects have a name you can refer to (e.g. the macro the workbook is being run from is designated ThisWorkbook so you don't need to set a separate variable to reference it). I think in your attempt, you couldn't work out how to switch between the workbook being opened and the workbook to paste data into. Does this work? :

Sub AggregateGenerator_V1()

Dim lCount      As Long
Dim lColCount   As Long
Dim LR          As Long

Dim wbResults   As Workbook
Dim wkDest      As Worksheet

Dim sPath       As String
Dim vData()     As Variant

    With ThisWorkbook
        Set wkDest = .Sheets("Aggregate Data")
        sPath = .path & "\Test\"
    End With
    lColCount = 4
        
    Application.ScreenUpdating = False
    
    On Error Resume Next
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyPath
        .FileType = msoFileTypeExcelWorkbooks
        
        If .Execute > 0 Then
            For lCount = 1 To .FoundFiles.Count
            
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=False)
                With ActiveSheet
                    LR = Application.Max(.Range("B" & .rows.Count).End(xlUp).row, .Range("C" & .rows.Count).End(xlUp).row)
                    vData = .Range("B1").Resize(LR, 2).value
                End With
                
                wbResults.Close False
                Set wbResults = Nothing
                
                LR = Application.Max(lColCount, wkDest.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column)
                wkDest.Cells(1, LR).Resize(LR, 2).value = vData
                Erase vData
                
            Next lconut
        End If

    End With
    
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    
    Set wkDest = Nothing
    
End Sub