VBA Run-Time Error in loop for pasting range values from one workbook to another

[bardophile]

WHAT I'M WORKING WITH: I have two workbooks, MacroTestingSheet2.xlsm is the source, and Target.xlsx is the destination. MacroTestingSheet2.xlsm has several worksheets in it that serve as the data source for the rest of the worksheets. Target.xlsx is a copy of all the remaining worksheets.

DESIRED GOAL: I want to paste the values of the range A15:D181 from each sheet in MacroTestingSheet2.xlsm to the sheet of the same name in Target.xlsx.

WHY?:The point of this is to not need to include all the data source worksheets when I send out copies to the various people who will be filling out the destination workbook sheets.

WHAT I'VE TRIED: After trying and failing to write code to match successive corresponding sheet names, I decided to work with the index numbers. I've determined that the index numbers of the sheets in MacroTestingSheet2.xlsm are 6 greater than the corresponding sheets in Target.xlsx.

The code I have written works for the first value of i. On the second iteration, I get a Run-time error.

As there are close to 200 worksheets for which I need to do this, each pair having different values than the other pairs, I really need a way to loop this process. Any help would be appreciated.

Sub PasteSpecialToTargetSheets()




Dim i As Integer




For i = 8 To 10
   
  
Windows("MacroTestingSheet2.xlsm").Activate
Workbooks("MacroTestingSheet2.xlsm").Worksheets(i).Range("A15:D181").Select 
'The line above is where I get the error "Run-time error '1004': Select method of Range class failed" on the second iteration
    Selection.Copy
    Windows("Target.xlsx").Activate
    Workbooks("Target.xlsx").Sheets(i - 6).Range("A15:D181").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




Next i
 
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub bardophile()
   Dim Dwbk As Workbook
   Dim Ws As Worksheet
   
   Set Dwbk = Workbooks("+book1.xlsm")
   
   For Each Ws In ThisWorkbook.Worksheets
      If ShtExists(Ws.Name, Dwbk) Then
         Dwbk.Sheets(Ws.Name).Range("A15:D181").Value = Ws.Range("A15:D181").Value
      End If
   Next Ws
End Sub
Public Function ShtExists(ShtName As String, Optional Wbk As Workbook) As Boolean
    If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function