VBA help

[bark01]

Hi,

I'm revisiting a bit of work a college did and can't get part of his macro to work. What it does is go to a specified folder and open up all the .xls documents in it in turn and copy and paste the values from a line in each .xls to a new row in another document.

The part where it opens the correct .xls files and copies the right rows works but it doesn't paste the values it finds to the correct .xls. It should copy the row of data "output row" then go back to the file where I start the macro "data master" and paste the values into the next blank row of the "input" sheet.

I've stepped through the code and the issue occurs when it trys to execute this command

Windows("Data Master").Activate

It just pastes the data over the date in the row it has copied it from, it doesn't switch workbooks. The workbook I run the macro from is called 'Data Master.xls'

The whole code is below.

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "C:\Documents and Settings\barto1a\Desktop\Field Service Sheets"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            '.Filename = "Book*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        
                        Sheets("Output").Select
                        Range("OutputRow").Select
                        Selection.Copy
                        Windows("Data Master").Activate
                        Sheets("Input").Select
                        Cells(lCount + 1, 1).Select
                        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                        
                        
                        wbResults.Close SaveChanges:=False
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

thanks

 

[VoG]

Hi. First thing to try

Workbooks("Data Master.xls").Activate