VBA Code for selecting window.

[bark01]

Hi,

I'm revisiting a bit of code a colleague did last year and I can't get it to work.

What it does is go to a folder and in turn open each .xls document in it and copy a row of data from each and then paste each row separately into a new line in the document where the macro is run from.

The row that is copied is a named range "Output Row", and the workbook where the macro is run from is called "Data Input.xls"

I've stepped through the macro and it opens up each file okay and copies the correct information, but when it comes to paste the values it doesn't go back to the data master workbook and just pastes the data over the same location it just copied it from.

The issue seems to be with the below line, the workbook the macro is run from is called 'Data Master.xls' and its open throughout the whole process.

Windows("Data Master").Activate

The full code is below, does anyone have any ideas?

I seem to recall we had a similar issue when it was first tried last year and the location of the data master was an issue, but I've tried running it from various folders (same folder as input files, desktop, other folder) and the same thing still occurs. I think the issue last time was running it from the same folder as the input files.

Sub Collect_sheets()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

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]

I replied in your other thread http://www.mrexcel.com/forum/showthread.php?t=546151

 

[JackDanIce]

n/a

 

[bark01]

I replied in your other thread http://www.mrexcel.com/forum/showthread.php?t=546151

When I didn't see it on the front page I thought I'd closed down chrome without submitting it, the board moves fast these days!

And you were right, I must have changed folder options since the macro was first run.

Thanks

 

[Norie]

You shouldn't need to activate any workbook(s) for this.

Just use the references declared and created earlier in the code.

                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        
                        wbResults.Sheets("Output").Range("OutputRow").Copy
                        
                        wbCodeBook.Sheets("Input").Cells(lCount + 1, 1).PasteSpecial Paste:=xlPasteValues                        
                      
                        wbResults.Close SaveChanges:=False