Copy from one sheet to another workbook based on multiple criteria VBA Excel

Question

I have two diffrent workbooks, From one of them i want to copy certain value based on multiple criteria.

In this example i will call each workbook wb1 and wb2. There will be shared wb links in the end of the post as example to make it more clear how it looks..

So in wb1 i have a list of names that also have their own sheet in wb2. When i open wb1 i want to copy the value under cell called "NPS" to each sheet in wb2 with the same name, In wb2 i have different month and a few other varibles that i have to add manual. So what i want it to do is copy value from NPS to the data under NPS and match it with the same Month that is also mentioned in wb1.

What i have so far is this code, but it doesnt realy make it.. Since i get an "subsribtion out of range" on this part Set sh2 = wb2.Sheets(c.Value). what im guessing is that it cant identify the sheets by the value even tho its named exaclty the same.

Sub NPS()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, wb2 As Workbook, fn As Range
Set sh1 = ThisWorkbook.ActiveSheet
Set wb2 = Workbooks.Open("F:\Excel\Chef\NPS Samtal 777 agentnivå.xlsx")
    For Each c In sh1.Range("C8", sh1.Cells(Rows.Count, 3).End(xlUp))
        Set sh2 = wb2.Sheets(c.Value)
        Set fn = sh2.Rows(24).Find(sh1.Range("B5").Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
               fn.Offset(1) = sh1.Range("E5").Value
        End If
    Next
End Sub

WB1: https://docs.google.com/spreadsheets/d/14CyC2CQWWH-Bxifw2EBni0Uj2YjlN-kIUVki2rle3LA/edit#gid=1398323909

WB2: https://docs.google.com/spreadsheets/d/1vcfnluE_PSm5dEEeHuBPA9XRoP-VPQRk3YDE7ONS-NE/edit?usp=sharing

IF anyone have any suggestions i would be very pleased.

//regards Daniel

AjCorneel AjCorneel · Accepted Answer · 2016-10-06T05:55:57

It seems like Excel is looking for the worksheet in the wrong file. You need to activate the right workbook before inserting the values. Like this:

Sub NPS()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, wb2 As Workbook, fn As Range
Set sh1 = ThisWorkbook.ActiveSheet
Set wb2 = Workbooks.Open("F:\Excel\Chef\NPS Samtal 777 agentnivå.xlsx")
    For Each c In sh1.Range("C8", sh1.Cells(Rows.Count, 3).End(xlUp))
         wb2.Activate       
         Set sh2 = wb2.Sheets(c.Value)
         Set fn = sh2.Rows(24).Find(sh1.Range("B5").Value, , xlValues, xlWhole)
         If Not fn Is Nothing Then
                fn.Offset(1) = sh1.Range("E5").Value
         End If
     Next
End Sub

On a side note: the worksheets in your ppsTest file are not aligned (the NPS -tal is in a different row in Example 1).

Copy from one sheet to another workbook based on multiple criteria VBA Excel

1 Answers