Copy Range.Value from one Workbook to Another on exactly the same place - non Volatile

[Thomazz]

Me again ... Probably a very noob question ...

I have a workbook with 150 Sheets (Workbook 1). Every sheet is a location ("Rome", "London", ...).

I have another workbook (Workbook 2) that has exactly the same sheets ("Rome", "London", ...).

I would like to copy the value of range C8:C16 from Workbook1.Rome to range C8:C16 Workbook2.Rome and I want to do that for all sheets. Not sure if it helps, but the SheetName is also present in Cell (A1) of every sheet.

I know I can write a macro that cycles through the Sheets and copy/pastes in to the other sheet. However, I think that slows down everything. Is there an easier solution to that ?

Small side-note : there are also sheets that are subtotals, I don't want to copy those. I only want to copy those ranges where there is the word "Data" in cells (O1") of every sheet [I have written Data or Subtotal in those sheets].

 

[JLGWhiz]

Since your data for each sheet may vary and some sheets may be eliminated by the value in cell "O1", I think the loop is probably the easiest way to do the job.

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh As Worksheet
Set wb1 = Workbooks(1)
Set wb2 = Workbooks(2)
    For Each sh In wb1.Sheets
        If sh.Range("O1") = "Data" Then
            sh.Range("C8:C16").Copy wb2.Sheets(sh.Name).Range("C8")
        End If
    Next
End Sub