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

Thomazz

New Member
Joined
Dec 28, 2017
Messages
19
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].
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,126,952
Messages
5,621,797
Members
415,856
Latest member
jimb2k

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top