With Within With

[ERLoft]

For some reason the following code isn't working. It's still in the Yield Forecast Sheet when it assigns the value of CurrentMonthPickup to a Range.

This particular macro is called by another macro, so there's lots of public variables at work, and all runs as expected, save for the fact that despite the With Workbooks(YieldSheet).Sheets(Currentsht) the data is staying in the workbook that it's being pulled from.

Sub PullForecastPickup()
'This macro pulls the forecast transient pickup from the Transient Booking Pace spreadsheet.

Dim DayOfWeek As String
Dim MonthNumber As Integer
Dim Today As Date
Dim RowNumber As Long

Today = Date
DayOfWeek = CStr(Format(Today, "DDDD"))
MonthNumber = CStr(Format(Today, "M"))

Call FindTransientBookingPaceWorkbook

With Sheets("Yield Forecast Sheet")
    For RowNumber = .UsedRange.Rows.Count To 1 Step -1
        If (Range("A" & RowNumber) Like DayOfWeek) Then
            If MonthNumber = CurrentMonthToUpdate Then
                Cells(RowNumber, 2).Activate
                CurrentMonthPickup = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, (LastDayOfMonth - EndHistoryDateToGrab))).Value
                With Workbooks(YieldSheet).Sheets(Currentsht)
                    Range(ActiveCell.Offset(11, (EndHistoryDateToGrab)), ActiveCell.Offset(11, (LastDayOfMonth - 1))).Value = CurrentMonthPickup
                End With
            ElseIf (MonthNumber + 1) = CurrentMonthToUpdate Then
                Cells(RowNumber, 3).Activate
            ElseIf (MonthNumber + 2) = CurrentMonthToUpdate Then
                Cells(RowNumber, 4).Activate
            Else
                Sheets("Input Sheet").Activate
                Exit Sub
            End If
        End If
    Next RowNumber
End With

End Sub

Not that it matters, but the ElseIf Then code isn't complete. I need to get the first If-Then working properly, then I'll worry about the ElseIf's.

Thanks in advance for any help!

 

[JoeMo]

I haven't looked at all your code, but a guess would be that Workbooks(YieldSheet) is not the active workbook and/or Sheets(Currentsht) is not the active sheet when you refer to Activecell. There can only be one active cell and that resides in the active sheet of the active workbook. Try making Workbooks(YieldSheet) and Sheets(Currentsht) active first.

 

[ERLoft]

Thanks Joe, that was indeed it.

I changed it to Workbooks(YieldSheet).Sheets(Currentsht).Activate and got rid of the with. Works like a charm now...