Set Rng появляется как Nothing

Прежде всего, вам нужно сделать свой код простым, например, у вас много .Activate. Это наверняка в какой-то момент приведет вас в замешательство.

вы должны сначала объявить все свои рабочие листы и рабочие книги следующим образом:

Sub Test()
dim book1 as workbook 'a workbook
dim book2 as workbook 'another workbook

dim SheetOfBook1 as worksheet 
dim SheetOfBook2 as worksheet 

set book1 = workbooks("NameOfWorkbook1.xlsm")
set book2 = workbooks("NameOfWorkbook2.xlsm")

set SheetOfBook1 = book1.worksheets("NameOfSheet")
set SheetOfBook2 = book2.worksheets("NameOfSheet")

'at this point you can check everything you want without activating something for example

 SheetOfBook1.range("A1") = SheetOfBook2.Range("A1") 'or whatever

'if you want to check if a value in book1 exists in book2 then do a loop

 dim cell as range

 for each cell in SheetOfBook2.Range("A1:A100).Cells
    If SheetOfBook1.range("A1") = Cell.Value Then
       msgbox "I founded what you are searching for"
    End If
 Next Cell
 End Sub

Таким образом, вы должны устранить все это .activate, использовать циклы и т. д. Что касается вашего примера, можете ли вы отредактировать свой вопрос и проиллюстрировать изображением, чего вы хотите достичь?

Изменить

Если вы хотите открывать книги на основе номера недели, у меня есть следующий код:

Sub Test()
Dim Main As Workbook
Dim Update As Workbook

Dim ForecastSort As Worksheet
Dim Forecast As Worksheet

Dim CheckIfOpen
Dim WeekNumber As String
Dim FirstDayInWeek

Dim FirstDayOfWeekRow As Long

Dim lRowUpdate As Long

Set Main = Workbooks("Main.xlsm")
Set ForecastSort = Main.Worksheets("Forecast_Sort")

'The code below will open the workbook which for name has the number week of today date automatically----------------------------------------------

 WeekNumber = WorksheetFunction.WeekNum(Date) + 1 'The requested week

 CheckIfOpen = IsWorkBookOpen("C:\Users\Erjon-PC\Desktop\Forecast\Wk" & 
 WeekNumber & "Update.xlsx") 'Checks if the update workbook is opened or not

 FirstDayInWeek = Date - Weekday(Date, vbUseSystem) + 2 'First day of requested week

 FirstDayOfWeekRow = ForecastSort.Range("B:B").Find(FirstDayInWeek).Row 'Finds the row of the start day of the requested week in main book


If CheckIfOpen = True Then
    Set Update = Workbooks("Wk" & WeekNumber & "Update.xlsx")
Else
    Set Update = Workbooks.Open("C:\Users\Erjon-PC\Desktop\Forecast\Wk" & WeekNumber & "Update.xlsx")
End If
'---------------------------------------------------------------------------------------------------------------------------------------------------
Set Forecast = Update.Worksheets("Forecast")

lRowUpdate = Forecast.Cells(Forecast.Rows.Count, "W").End(xlUp).Row 'Last row in column W in update book

Forecast.Range("W2:Y" & lRowUpdate).Copy

ForecastSort.Range("H" & FirstDayOfWeekRow).PasteSpecial xlPasteValues

Update.Close savechanges:=False

End Sub

Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0

Select Case ErrNo
Case 0:    IsWorkBookOpen = False
Case 70:   IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function

Приведенный выше код откроет книгу, в названии которой указан номер недели. Сегодня она откроет Wk11Update.xlsx, а на следующей неделе откроет Wk12Update.xlsx.

Если вы хотите открывать книги, в которых указаны будущие даты, просто добавьте +1 или больше к этому коду:

WeekNumber = WorksheetFunction.WeekNum(Date) + 1 'The requested week

Затем в открытой книге он найдет последнюю строку с данными в столбце W, скопирует 3 столбца, начиная с W, и вставит их в столбец H основной книги. Данные будут вставлены в строку, где находится первый день запрошенной недели.