Confused about referencing multiple object code rows in other worksheets?

I'm new to VBA coding in Excel and am trying to figure out how to do this, but I am running lost.

Each month I will have a workbook containing various worksheets, each worksheet representing an individual account. The worksheets will be named by account number, like '123456'. I will then import a worksheet titled 'ocd 123456'. I need to create some code that recognizes the 'ocd 123456' worksheet and adds certain values together to copy to cells in the '123456' worksheet. There may be 10+ account numbers in each workbook, corresponding to an equal number of imported 'ocd ######' worksheets in the same workbook.

The 'ocd 123456' worksheet will have a variable amount of rows depending on account and that month's transactions. The 'ocd 123456' worksheet will have two columns, column A with a three-digit object number followed by the corresponding object title and column B containing a monetary value. Additionally, a single cell in '123456' will likely need the sum of various object code values from multiple rows of 'ocd 123456'.

For example:
Cell C22 in '123456' should equal the sum of object codes 901-927 but not 921 (or 901-920 & 922-927). Each month will likely not have all of the these object codes, just a few.

123456

Please Login or Register  to view this content.

ocd 123456

Please Login or Register  to view this content.

Therefore, C22 in '123456' should equal 145 (50+75+20).

I would geatly appreciate any help with this or any links or recommendations of books that may help me along the way.

Thanks,
Collin

Can someone please help me out?

Slunk,

You need to take the left 3 characters of the entry in column A - you can
then categorise the entries. There are various ways to do it - here's a
suggestion using Collections - using the object code as a 'key':

Sub getTotals(aShtName As String)
Dim inShtName As String
Dim lastRow As Long
Dim aRow As Long
Dim objID As String
Dim aValue As Double
Dim aCollection As Collection
Dim total As Double
Dim x As Integer

inShtName = "ocd " & aShtName
Set aCollection = New Collection

With Sheets(inShtName)
' get the last row
lastRow = .Range("A65536").End(xlUp).Row
' then read in the values for each row
For aRow = 1 To lastRow
objID = Left(.Cells(aRow, "A"), 3)
aValue = .Cells(aRow, "B")
aCollection.Add key:=objID, Item:=aValue
Next aRow
End With

' now create totals on the required object codes
total = 0
On Error Resume Next
' for object codes 901 to 920
For x = 901 To 920
total = total + aCollection(Format(x, "000"))
Next x
' for object codes 920 to 927
For x = 922 To 927
total = total + aCollection(Format(x, "000"))
Next x
On Error GoTo 0

' put the total in cell C22
Sheets(aShtName).Range("C22") = total
Set aCollection = Nothing

End Sub

This could be called with:

Sub testTotals()
getTotals "123456"
End Sub

The above is with the different sheets in the same workbook. You would need
to change the:
With Sheets(inShtName) etc
to something like:
With Workbooks("myworkbook").Sheets(inShtName)
to distinguish the different workbooks.

This assumes that there will not be any duplicate object codes in your
imported list.

HTH

Tim


"slunk" <[email protected]> wrote in
message news:[email protected]...
>
> I'm new to VBA coding in Excel and am trying to figure out how to do
> this, but I am running lost.
>
> Each month I will have a workbook containing various worksheets, each
> worksheet representing an individual account. The worksheets will be
> named by account number, like '123456'. I will then import a worksheet
> titled 'ocd 123456'. I need to create some code that recognizes the
> 'ocd 123456' worksheet and adds certain values together to copy to
> cells in the '123456' worksheet. There may be 10+ account numbers in
> each workbook, corresponding to an equal number of imported 'ocd
> ######' worksheets in the same workbook.
>
> The 'ocd 123456' worksheet will have a variable amount of rows
> depending on account and that month's transactions. The 'ocd 123456'
> worksheet will have two columns, column A with a three-digit object
> number followed by the corresponding object title and column B
> containing a monetary value. Additionally, a single cell in '123456'
> will likely need the sum of various object code values from multiple
> rows of 'ocd 123456'.
>
> For example:
> Cell C22 in '123456' should equal the sum of object codes 901-927 but
> not 921 (or 901-920 & 922-927). Each month will likely not have all of
> the these object codes, just a few.
>
> 123456
>
> Code:
> --------------------
>
> A B C
> 20
> 21
> 22 Equipment Purchases =(sum of object codes 901-920 &
922-927 from 'ocd 123456')
> 23
> 24
>
> --------------------
>
>
>
> ocd 123456
>
> Code:
> --------------------
>
> A B
> 1 001 - Salary 8,000
> 2 023 - Copies 20
> 3 901 - Laser Printers 50
> 4 908 - Light Bulbs 75
> 5 921 - Overhead 100
> 6 927 - Staplers 20
>
> --------------------
>
>
> Therefore, C22 in '123456' should equal 145 (50+75+20).
>
> I would geatly appreciate any help with this or any links or
> recommendations of books that may help me along the way.
>
> Thanks,
> Collin
>
>
> --
> slunk
> ------------------------------------------------------------------------
> slunk's Profile:
http://www.excelforum.com/member.php...o&userid=31957
> View this thread: http://www.excelforum.com/showthread...hreadid=516795
>