with statement

[Blake 7]

Hi Guys,

I wonder if someone could take the time to help..

I would like to amend this code slightly by forcing it to perform the exact same function on an additional tab called Forecast.

So where the code says With Worksheets("Drill Down") I want it to say With Worksheets("Drill Down") and worksheet Forecast, however I am not having much success!

Maybe it's something like

Worksheets("Drill Down").select
WorkSheets("Forecast").select

Thanks for looking

Option Explicit

Sub dRILLDOWN()
 Dim x, i As Long, y, ii&, j&, k&
Application.ScreenUpdating = 0
  With Worksheets("Drill Down")
    y = .Range("F8:S" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
End With
 With Worksheets("Source Data")
    x = .Range("A1:B" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
End With
 
With Worksheets("Drill Down")
     
    For i = 2 To UBound(y, 1)
         For j = 1 To UBound(x)
            If y(i, 2) = "Actual" Then
         
                If y(i, 1) = x(j, 1) Then
                     For ii = j + 1 To UBound(x)
                          k = 7
                           Do Until x(ii, 1) = "TOTAL"
                                k = k + 1
                                .Cells(i + 7, k) = x(ii, 2)
                                ii = ii + 1
                            Loop
                           Exit For
                     Next
                End If
            End If
        Next
    Next
End With
 
Application.ScreenUpdating = True
End Sub

[alansidman]

Add the following lines of code to your Dimensions

Dim SheetsArray as Sheets
Set SheetsArray = ActiveWorkbook.Sheets(Array("Drill Down", "Forecast"))
Dim SheetObject as Worksheet

Replace the With Statement with

For each SheetObject in SheetArray

Replace the End With Statement with

Next SheetObject

[6StringJazzer]

The minimal change to make to do this operation on two different sheets is to add the worksheet as an argument to your sub, then call it twice. See code highlighted in red. Did I understand the question?

Sub Drill2()
   dRILLDOWN Worksheets("Drill Down")
   dRILLDOWN Worksheets("Forecast")
End Sub

Sub dRILLDOWN(WS As Worksheet)
 Dim x, i As Long, y, ii&, j&, k&
Application.ScreenUpdating = 0
  With WS
    y = .Range("F8:S" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
End With
 With Worksheets("Source Data")
    x = .Range("A1:B" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
End With
 
With WS
     
    For i = 2 To UBound(y, 1)
         For j = 1 To UBound(x)
            If y(i, 2) = "Actual" Then
         
                If y(i, 1) = x(j, 1) Then
                     For ii = j + 1 To UBound(x)
                          k = 7
                           Do Until x(ii, 1) = "TOTAL"
                                k = k + 1
                                .Cells(i + 7, k) = x(ii, 2)
                                ii = ii + 1
                            Loop
                           Exit For
                     Next
                End If
            End If
        Next
    Next
End With
 
Application.ScreenUpdating = True
End Sub

[Kaper]

may be try:

Sub dRILLDOWN()
call realdRILLDOWN("Drill Down")
call realdRILLDOWN("Forecast")
end sub


Sub realdRILLDOWN(mysheet as string)
 Dim x, i As Long, y, ii&, j&, k&
Application.ScreenUpdating = 0
  With Worksheets("Drill Down") 'may be here  With Worksheets(mysheet)
    y = .Range("F8:S" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
End With
 With Worksheets("Source Data")
    x = .Range("A1:B" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
End With
 
With Worksheets(mysheet)
 ' the rest

edit: almost the same way ;-) but as always slightly different way to skin the cat.
Notice other type of argument, other way to call procedure with argument and my doubts about serching for value of y - shall it be always taken from Drill Down sheet? Can imagine that it shall do this way. Or shall it be determined as other value for Forecatst sheet - also pausible.

[Blake 7]

Awesome - many thanks guys for taking the time to help. I appreciate the different solutions! Solved!

Jeff, you did understand!! good to hear from you.