For Each Worksheet (but not all of them)

[ElBombay]

Hi,

My current client has 32 department-level P&L reports that are uploaded to XL each month using Workbooks.OpenText. The data is then copied from each RPT file and pasted into a single, standardized P&L.xls file that has a different work sheet for each dept. Someone at the firm has recorded the keystrokes to a macro for each department and set up a controling macro that calls each dept-macro explicitly. These macros use identical source and target cells; they differ only in the names of the text-reports and the tabs on which they are presented.

I am writing a standard template that will substitute variables for these minor differences. Apart from asthetics, several of the macros have already been corrupted (or never worked in the 1st place). Maintainability is also an issue as depts get reorganized.

I want to use a standard For Each loop to read these reports but there are other sheets in the P&L workbook (summaries, title pages, etc.) that I would have to skip in the loop. The code here is what I would run if the w/b was exclusively for the reports.

  Sub Read_EliteRpt()
' Move ELBS report-data into presentation format
' 9/27/06 by Jim
'
Dim ws As Worksheet

    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
        Call Dept_PandL(cRptName, cXLtab)

    Next ws
    Application.ScreenUpdating = True

End Sub

Instead of being driven by the 'ActiveWorkbook.Worksheets' array, how can I

1) get the loop to call Dept_PandL() for only certain w/s in P&L.xls,
2) associate each w/s (tab-name) with the RPT file needed and
3) build a dedicated array/table and have the loop read it, if that's the solution called for here

An alternative to the table mentioned in step 3 might be some kind of flag and an If-test inside the loop but this is definately a problem where I should first ask somebody who knows. The help is always appriciated. Thanks.

 

[dcardno]

I assume that you have (at least one) sheet that can be used for control purposes, even if it will be hidden from the end user . I would include a table there, with the sheet names in one column and an indicator (say an "X") if the department is to be imported from an RPT file in the next column, then the name of the RPT file and the name of the department in the next columns. For completeness you could include the sheetnames for summaries, etc - these would have no "X" in the import column, and no RPT file name / department name (although they might have other indicators...)

Loop down through this array - either by counting rows and incrementing down; by a For each... Next loop applied against all the cells with tab names; or by stepping down the column and testing (and stopping on) a value of "" (or some other "stop" value). At each iteration the values for your Dept_PandL(RPTName, XLTab) routine can be picked up by reading the values from the table, offset appropriately. With an AIR CODE warning, it would look something like this, assuming that the top-left entry in the table has a defined name of [DataList]:

dim i as integer
i = 0

Do
if [datalist].offset(i,1) = "X" Then
  Call Dept_PandL([datalist].offset(i,2), [datalist].offset(i,0))
End If
if [datalist].offset(i,1) = "" Then Exit Do
i = i + 1
Loop

The advantage of leaving this in an Excel table (even if it has to be "unhidden" by a command button) is that your client can probably maintain it themselves if they add a new department or change the name format of the RPT files - if the values are hidden in the VBA code they will likely not be able to do so (although that can be good for job security... )