How to copy values from one OPEN workbook (with variable file name) to another

Hello there :-)

Every month I receive a batch of new files with names such as Analysis_Report_0001, 0002, 0003 etc. The Analysis_Report string is always there but the numbers are changing - you get the idea.

Then I have a file Analysis Summary where I need to input certain values from the Analysis_Report files.

Here's the code that works (I have both files already open and the macro is in the Analysis Summary file).

 Sub Macro1()
'
' Macro1 Macro
'

'   Windows("Analysis_Report_0001.xlsx").Activate 
    Range("Q3:Q10").Select
    Selection.Copy
    Windows("Analyses Summary.xls").Activate
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False

End Sub

What I need to do is to have a macro nested in the Analysis Summary file, that would activate any OPEN workbook that contains the "Analysis_Report" string in its file name, copy the selected range, and paste values to the first empty cell in the column C.

Now, I don't need the macro to open the Analysis Report file, as I already have it open, and I need to do without references to C: stuff - the path would change every month anyway.

I've spent the last two days comming out with a solution, and I'm going gray by now :-)

Any help will be much appreciated.

Please wrap the code in your post with the CODE tags. Select your code and click on the # button.

Try this

Public Sub UpdateAnalysisSummary()
'#
'# declare private variables
'#
     Dim xlsWorkbook As Excel.Workbook
'#
'# loop for all open workbooks and select those workbooks where the name
'# of the workbook contains 'Analysis_Report'
'#
     For Each xlsWorkbook In Application.Workbooks
          If xlsWorkbook.Name Like "Analysis_Report*" Then
               With ThisWorkbook
                    .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(8).Value = xlsWorkbook.Range("Q3:Q10").Value
               End With
          End If
     Next xlsWorkbook
End Sub

Try this

Public Sub UpdateAnalysisSummary()
'#
'# declare private variables
'#
     Dim xlsWorkbook As Excel.Workbook
'#
'# loop for all open workbooks and select those workbooks where the name
'# of the workbook contains 'Analysis_Report'
'#
     For Each xlsWorkbook In Application.Workbooks
          If xlsWorkbook.Name Like "Analysis_Report*" Then
               With ThisWorkbook.Activesheet
                    .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(8).Value = xlsWorkbook.ActiveSheet.Range("Q3:Q10").Value
               End With
          End If
     Next xlsWorkbook
End Sub

@Kitko,

Please test with the code from post #5, there were some typing errors which I have corrected.

Maybe this

Sub Macro1()
 Const sSheetName As String = "Name of worksheet to paste to" '<-- Change here for your needs
 Dim wb As Workbook

    For Each wb In Workbooks
        If wb.Name Like "Analysis_Report*" Then
            With ThisWorkbook.Worksheets(sSheetName)
                wb.Range("Q3:Q10").Copy .Cells(Rows.Count, "c").End(xlUp).Offset(1)
            End With
        End If
    Next
End Sub