Copying from 1 Workbook and multisheets to Another Workbook and multisheets

[kewiopex]

I am very new in the VBA coding area and I would like to learn some more. I am trying to perfect the coding that will allow me to copy data ranges from 2 sheets ( Unit 1, Unit 2) into another workbook with the same sheet names but same data ranges and with a paste special of value only. When I run the code it gives me a runtime error where object does not support this operation or method. Any help would be greatly appreciated.


Sub TestWB()
Dim sourceWB As Workbook
Dim i As Long
Set sourceWB = Workbooks("TestBP.xls")
For i = 1 To Worksheets.Count
sourceWB.Worksheets("Unit" & i).Range("B2").Copy ThisWorkbook.Worksheets("Unit" & i).Range("B2").PasteSpecialxlPasteValues
sourceWB.Worksheets("Unit" & i).Range("B7").Copy ThisWorkbook.Worksheets("Unit" & i).Range("B7").PasteSpecialxlPasteValues
Next i

End Sub

[Bob Phillips]

Sub TestWB()
Dim sourceWB As Workbook
Dim i As Long

Set sourceWB = Workbooks("TestBP.xls")
With sourceWB

For i = 1 To Worksheets.Count

.Worksheets("Unit" & i).Range("B2").Copy
ThisWorkbook.Worksheets("Unit" & i).Range("B2").PasteSpecial Paste:=xlPasteValues
.Worksheets("Unit" & i).Range("B7").Copy
ThisWorkbook.Worksheets("Unit" & i).Range("B7").PasteSpecial Paste:=xlPasteValues
Next i
End With
End Sub

[kewiopex]

You are the best. Worked like a charm and I thank you for all your time and details. You will save me a ton of tie.

[SamT]

Sub xld()
'xld's sub modified by SamT
'Added Multiple Range capability with only one CopyPaste line inside a loop.

Dim RngAddies As Variant
Dim i As Long
Dim r As Long

RngAddies = Array("B2", "B7") 'Separate each additional address by a comma.

With Workbooks("TestBP.xls")

For i = 1 To Worksheets.Count
For r = LBound(RngAddies) to UBound(RngAddies)
.Worksheets("Unit" & i).Range(RngAddies(r)).Copy
ThisWorkbook.Worksheets("Unit" & i).Range(RngAddies(r)).PasteSpecial Paste:=xlPasteValues
Next r
Next i

End With
End Sub

One line alternative to two line CopyPaste above.

ThisWorkbook.Worksheets("Unit" & i).Range(RngAddies(r)) = .Worksheets("Unit" & i).Range(RngAddies(r))

[kewiopex]

Sub xld()
'xld's sub modified by SamT
'Added Multiple Range capability with only one CopyPaste line inside a loop.

Dim RngAddies As Variant
Dim i As Long
Dim r As Long

RngAddies = Array("B2", "B7") 'Separate each additional address by a comma.

With Workbooks("TestBP.xls")

For i = 1 To Worksheets.Count
For r = LBound(RngAddies) to UBound(RngAddies)
.Worksheets("Unit" & i).Range(RngAddies(r)).Copy
ThisWorkbook.Worksheets("Unit" & i).Range(RngAddies(r)).PasteSpecial Paste:=xlPasteValues
Next r
Next i

End With
End Sub

One line alternative to two line CopyPaste above.

ThisWorkbook.Worksheets("Unit" & i).Range(RngAddies(r)) = .Worksheets("Unit" & i).Range(RngAddies(r))

[kewiopex]

SamT, this is so elegant! I am learning a lot from these superb responses. I have another issue that I think may reside in another question. I have come across another situation today whereby the worksheet names are different in each workbook but I wish to loop through the worksheets in one workbook and copy ranges and pasting in different worksheet names in another workbook. I noticed that coding names for worksheets use the Sheet1..notation but, in my example the worksheets in 1 workbook are not the same in the other workbook.

[SamT]

in my example the worksheets in 1 workbook are not the same in the other workbook.
How are they co-related?

[kewiopex]

Worksheet names are slightly different in each workbook and not in the same order...eg Unit 1 , Unit 2 versus B-1 B-2. The data from source does not directly map over as stated in the original issue. Does this adequately describe the nuance?
Thank you once again for your tremendous effort.