VBA for copying selected cells from all opened work books

[lnlarxg]

Hi all, what a fab wedsite! first time posting hope you can help

I am trying to write a VBA for copying a few cells from various opened workbooks (number of workbook varies depending on the number of reports I get on the day), and pasting them onto my active workbook

These cells are always in the same position on all workbooks.

I hope someone can help me on making this VBA for regconising all opened workbooks and copying the cells for these xls then paste onto my active workbook.

Much Much appreciated

Lnlarxg

 

[Trevor G]

Welcome to the forum,

You could work with something like this.

Sub copyData()
Dim ws As Workbook
For Each ws In Workbooks
ActiveWorkbook.Sheets(1).Range("A2:B5").Copy
ActiveWorkbook.Close
Windows("Book1").Activate
Range("A6000").Select
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).PasteSpecial xlPasteAll
ActiveWindow.ActivateNext
Next ws
End Sub