Macro to pull data from workbooks and return double sided accounting entry

hi JediAccountant, welcome to Excelforum, it would much more helpful to have a sample file with the data and result expected on it

Sorry.

What is the best way to provide this sample? I could upload it to a website, is there a preferred one? I dont see a way to attach it to a post.

I figured out how to make an attachment. Attached is the file.

Questions:
- Do you need result table on each sheet as shown?
- Is it done from a scratch or some data is already there? Where do you take GL value from?

Yes, for every sheet I would want to do this. Some sheets can have dozens of records, for simplicity I only used a few. The format of them all should be the same.

The GL, FUND, Department, project, etc on the attachment are all fake. Even the fund# is fake. I get the value of these from our accounting codes. They are different in real life.

please check attachment, run code "test"

Columns filled by the code: Date, Fund#, GL#(offsett record only),$amount

I think it will be easier if I take some templates out of the workbook and just change all personal information and real numbers.

Give me an hour to put something together.

please check attachment

Under the Forum rules the posted code should be enclosed in special tags, see Forum rules for details.

Sorry about the mistake, I edited my post to hopefully be in compliance with the rules.

I also want to thank you for all the help so far.

Anyway I copied one of the templates and hid all real info so the attachment is the EXACT same format as the workbook. The only difference is some info not related to the formula is gone, and names are changed. Some worksheets have 50+ records. I realize I should have done this to begin with, and didnt consider how small differences could change things. I am sorry for the newbie mistakes.

I made some highlights. These highlights are not in the workbook i need the Macro for, nor is there any colored text.


Please note that all columns highlighted in red the numbers are coded to specific GL accounts. These GL accounts are just numbers. The Department and Project is all the same. The reason for this file is to pull all this information, create an upload file, and use the file in our Accounting system to make a journal entry.

The wanted results are also not in the actual workbook. I added that to show how the information is presented and what is needed.

I noticed your #9 post after posting my #10

on Sheet 1A the rows 21:22 are hidden but have data in them. Do we process them or we need visible data only?

I didnt know there was hidden data. After some research I realized that any dates past 9/30/2011 were hidden because they have already been fixed. So, this only needs to be done for dates up to 9/30/2011 and shouldnt be done after. If the macro pulls everything I can always filter out later dates.

please check attachment, run code "test". The code would process visible data only.

I will run it Monday morning and let you know how it goes.

In the meantime could you recommend me a good online resource for learning VB?

you are using it right now

hi JediAccountant,

why to delete amounts in the sample file if it's enough to delete header to remove what they reference to? What confidential information holds a number without context of it's use? With numbers as is I can test the code to reproduce error you get your end.

Changes to the code:

1. Before pasting result table the code would unmerge cells.
2. Amount issue: you have hidden columns from 6th till 8th. The Amount and Project are hidden in them.

Option Explicit
Option Compare Text
Sub test()

Dim iheaderarr, sh, fundname, cl, idate
Dim fundrng As Range, lastrowrng As Range
Dim lrow As Long, i As Long, j As Long

iheaderarr = Array("Date", "GL#", "FUND#", "Department#", "Project#", "$amount")

Application.ScreenUpdating = 0

For Each sh In ThisWorkbook.Sheets
    With sh
        Set fundrng = .UsedRange.Find("fund:", , xlValues, xlPart)
        If Not fundrng Is Nothing Then
            Set lastrowrng = .UsedRange.Find("total", , xlValues, xlPart)
            If Not lastrowrng Is Nothing Then
                lrow = lastrowrng.Row - 1
                If lrow > 16 Then
                    fundname = fundrng.Offset(, 1).Value
                    ReDim result(1 To (lrow - 16) * 12, 1 To 6)
                    For i = 17 To lrow
                        If .Rows(i).Hidden = 0 Then
                            idate = .Cells(i, 3)
                            For Each cl In .Range("d" & i & ":n" & i)
                                With cl
                                    If IsNumeric(.Value) Then
                                        If .Value <> 0 Then
                                            j = j + 1
                                            result(j, 1) = idate
                                            result(j, 3) = fundname
                                            If .Column <> 14 Then
                                                result(j, 6) = .Value * (-1)
                                            Else
                                                result(j, 6) = .Value
                                                result(j, 2) = "OFFSETT"
                                            End If
                                        End If
                                    End If
                                End With
                            Next
                        End If
                    Next
                End If
            End If
            Set fundrng = Nothing
            Set lastrowrng = Nothing
        End If
        If j > 0 Then
            .Range("b" & lrow + 8).Resize(j + 1, 6).UnMerge
            .Range("b" & lrow + 8).Resize(, 6) = iheaderarr
            .Range("b" & lrow + 9).Resize(j, 6) = result
            j = 0
        End If
    End With
Next

Application.ScreenUpdating = 1

End Sub

you are right, they were hidden and it works after emerging!

I am so bad at this.

The reason I hide everything is because my organization is VERY strict about this information. If I used real information I could lose my job, so I hide, or change anything, that could be considered sensitive. I am probably overdoing it but I am just playing it safe.

Anyway, you did it! Thank you so much!

I just have some follow up questions for the code that dont require any new coding on your part.

I want to add the GL to the code now. So for each column that pulls information I want to have it add it as the GL number. I have reattached Template #2 showing what I mean, if you look at the red highlighted part you will see what I am trying to do. Where would I modify your macro to do this?

please check attachment, run code "test", the new lines are commented in the code

please check attachment

I expected that question about one sheet output but somehow you kept silent ...

Your macro works!

I had to make some small changes to the worksheets, updating formulas, etc but it gave the results that were needed.

I would rep you a hundred times if I could.