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

**JediAccountant** · 08-22-2013, 04:16 PM

This is my first post so I am sorry if I making any mistakes in etiquette.

Anyway I have an excel workbook with about 200 worksheets. Every workseheet contains a report like this:

Capture1.PNG

For every value in Columns E,F,G,or J there would need to be an entry in a row in this format: Date-GL-Fund#-Department-project-$amount. There would then need to be an offsetting entry. This template would populate like this, notice that the signs are reversed:

Capture2.PNG

In the end everything zeros out.

There are about 200 worksheets like this. The number of records will vary and the Fund # will be different in each fund.

Does anyone have any suggestions, resources or macros that would be useful?

**watersev** · 08-22-2013, 04:32 PM

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

**JediAccountant** · 08-22-2013, 05:03 PM

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.

**JediAccountant** · 08-22-2013, 05:09 PM

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

**watersev** · 08-22-2013, 05:24 PM

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?

**JediAccountant** · 08-22-2013, 05:33 PM

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.

**watersev** · 08-22-2013, 06:21 PM

please check attachment, run code "test"

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

**JediAccountant** · 08-23-2013, 09:41 AM

Those are the exact same results I needed.

It isnt working on the real file though, I imagine I need to make some changes.

Also I realized there was some hidden columns in my test code so the earlier file may have been wrong. I have reattached the file with the new columns that I didnt include in my template. Also I moved fund # to D* where it is in every template. When I added the new columns and moved the fund the Macro stopped working. So I just need to update it.

Debugger says cant execute code in break mode.

result(j, 6) = data(i, n) * (-1)

Here is the code:

Please Login or Register  to view this content.

**JediAccountant** · 08-23-2013, 09:53 AM

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.

**watersev** · 08-23-2013, 09:56 AM

please check attachment

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

**watersev** · 08-23-2013, 09:58 AM

I noticed your #9 post after posting my #10

**JediAccountant** · 08-23-2013, 10:48 AM

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.

**JediAccountant** · 08-23-2013, 12:25 PM

Your attachment works in this file but when I apply the macro to the actual file I get a Run-Time error '13' Type Mismatch

result(j, 6) = data(i, n) * (-1)

I am going to apply it to the template I attached above and see if it works on the template.

EDIT:

Do I only copy this code

Please Login or Register  to view this content.

EDIT 2: When I run the code on the template file nothing happens. I believe it executes but no results come back or any messages.

**watersev** · 08-23-2013, 03:28 PM

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

**JediAccountant** · 08-23-2013, 04:01 PM

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.

**watersev** · 08-23-2013, 05:07 PM

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

**JediAccountant** · 08-23-2013, 05:44 PM

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?

**watersev** · 08-23-2013, 06:09 PM

you are using it right now

**JediAccountant** · 08-26-2013, 09:39 AM

Ok, this almost worked completly just a few issues.

Attached is the result of running this data on the actually workbook. Please note that I I removed all the actual information, but the macro was run before removing it.

It works great on the file you gave me.

When I run this on the real template it does pull the correct information with date and fund, however the first few rows have formating issues that have to deal with merged cells please see attachment. Unmerging all the cells before running the macro fixes.

When I run the macro no dollar amount is shown. So I can see the date and the GL# but not the number for the actual numbers pulled from the cells. It actually vuts of after Department#. So in the template file you have columns for

Date GL# FUND# Department# Project# $amount

In the actual workbook and even after unmerging the columns that the macro creates are:

Date GL# FUND# Department#

When I run the code I get this error: Run Time error "13": Type Mismatch

and then debugger highlights this

Please Login or Register  to view this content.

**watersev** · 08-26-2013, 03:13 PM

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.

Please Login or Register  to view this content.

**JediAccountant** · 08-26-2013, 03:50 PM

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?

**watersev** · 08-26-2013, 05:13 PM

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

**JediAccountant** · 08-26-2013, 05:52 PM

Works perfectly.

You are awesome!

If you were in my area I would take you out to lunch.

Can you help me with one last thign? If it is too much dont worry you have done enough.

The last thing I need is to put the results of this macro in all the worksheets into one worksheet starting with the first worksheet, and then having the next worksheet starting in the row after the last one stopped.

Please see tab upload file.

**watersev** · 08-26-2013, 06:19 PM

please check attachment

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

**JediAccountant** · 08-27-2013, 11:16 AM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Macro to Pull Data from Multiple Workbooks to One Master Workbooks

recorded the macro:printing double sided

[SOLVED] How to print single sided versus double sided?

How can I print two sided documents in Excel?:print double-sided,

Bookmarks

Bookmarks

Posting Permissions