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

Vote count: 0

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: http://ift.tt/2dsDtgj

WB2: http://ift.tt/2dKRBN4

IF anyone have any suggestions i would be very pleased.

//regards Daniel

asked 27 secs ago

D Hellenborg

Let's block ads! (Why?)

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