Your technology certification is waiting. Enroll in Cloud Class ®
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long
Dim J As Long
Dim K As Long
Dim col As Long
Dim arrCols As Variant
Dim arrRows As Variant
Dim Res As Variant
Set wsTotals = Sheets.Add
wsTotals.Range("B1:D1").Value = Array("ROW8", "ROW18", "ROW54")
arrCols = Array("HX:SX", "TX:AEX", "AFX:AQX", "ARX:BCX", "BDX:BOX", "BPX:CAX")
arrRows = Array("Total Income", "Gross Profit", "Net Income")
For I = 1 To 50
strShName = "CA" & Format(I, "000")
col = 2
.Cells(I + 2, 1).Value = strShName
If Evaluate("ISREF('" & strShName & "'!A1)") Then
For J = LBound(arrCols) To UBound(arrCols)
For K = LBound(arrRows) To UBound(arrRows)
Res = Application.Match(arrRows(K), Sheets(strShName).Columns(1), 0)
If Not IsError(Res) Then
.Cells(I + 2, col).Formula = "=SUM(" & strShName & "!" & Replace(arrCols(J), "X", Res) & ")"
col = col + 1
.Cells(I + 2, 2).Resize(, 3).Value = "#N/A"
Open in new window
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.