Extract from raw data into form

[chrisTXACC]

Hello,

I am a new user of this forum and wish to seek counsel regarding my hope to improve a process I have to go through.

I currently work with a very large data set with specific transactions including a broad range of criteria per row of information. When using this data, I have been using a pivot table to narrow it down to specifically what columns I need to include and then filtering a column to a specific site that includes all transactions regarding that site then subtotaling on a date basis. I then copy that information to a tax return for each site (77 to be exact) on a monthly basis. Ultimately, my goal is this: have a raw date sheet that I can import from the export function in our software. Have each form set up so that the data can be refreshed and each form auto-populates the data on each form based off the current raw data included in that sheet. I had tried a work around with inserting a pivot table into each forum however, setting up the formatting for each pivot table wasn't the largest issue, the change in rows of data would either expand the form (basically inserting new rows), or reduce total rows because the volume of transaction were reduced for the current month shrinking the total size of the form.

I am very new to utilizing advanced excel functions but am very eager to learn. Any questions that would assist in resolving my situation are highly welcomed!

Thanks!

[Leith Ross]

Hello chrisTXACC,

Welcome to the Forum!

It is difficult to provide you with any real guidance on this problem. The physical layout, formulas, pivot tables etc. must all be considered. Since this information is for accounting purposes and you are the fiduciary, the workbook is probably off limits due to confidentiality. However, if it is possible for you to create a faithful redacted version of your workbook and post it then we can offer you lots of ideas.

[chrisTXACC]

Thank you so much for the response. The data I am working with can easily be redacted/changed to meet confidentiality standards. I will work on this as time permits. With it being Christmas, it may take me an extra day to generate this request.

Thanks for the guidance.

[chrisTXACC]

Okay, so I know this is very limited but keep in mind that the only difference is the amount of data provided vs the actual amount I'd be working with. I have a form that must be submitted monthly that includes every transaction associated with one criteria ("DestinationDescription"). The column headers in the raw data sheet attached is what comes out of the system and the only columns I've entered data in are the ones that this process actually considers. I have 50+ forms that must be submitted as that is how many different "DestinationDescription" or locations might you think there are. My goal is to have the are in the "Form" sheet that is highlighted auto populate any data that is relevant to the specific criteria, lets say "Loc-name (123) 0987", the other columns are the data that needs to come over to the form once whatever formula determine that the cell in column "DestinationDescription" =Loc-name (123) 0987. Each month, the number of transactions to each location could vary to an extent so that is why I would like the entire form to be auto populated and if part of it is empty due to minimal transactions to remain blank. I'm not sure what kind of macros could be made here, however, this is a process I would like to stream line and turn a 5 hour process into minutes by importing to new outputted raw data into the worksheet then refresh all the forms and then be able to simply print said forms for submission from a minimal click approach.

If any further explanation is needed, please ask!

ATTACHMENT

[Leith Ross]

Hello chrisTXACC,

Thank you for taking the time to create the copy and posting it. After looking at this, I have a few questions.

1. Do you want the number of rows under Purchases (rows 21-52) to remain the same? That is it will remain 30 row regardless of the filtered data.
2. Will Purchases only be for one carrier and arranged by date?
3. Will all forms be kept in the same workbook?

[Leith Ross]

Hello chrisTXACC,

This workbook will extract and copy the raw data to an individual worksheet with the name in the destination description column. You run the macro by using the keys Ctrl+Shift+X. The data is limited to 30 rows (what you have for purchases on the Form). The data is over written each time the macro runs.

The Form worksheet is now hidden. This is done because it is being copied when a new worksheet is to be added. The macro below is what has been added to the attached workbook.

Please Login or Register  to view this content.

[chrisTXACC]

Thanks for the response:

As this form is basically a replica of an actual form; those rows would need to remain the same in the sense that they do not case the sheet to expand to a two sheet form once printed. To finish the rest of my answer I need to answer your second question then loop back around. Essentially, all purchases assigned to each "DesitinationLocation" need to be applied to the form associated with the corresponding location. Sorted in by date of the transaction. It hasn't to my knowledge, been an instance where there have been more transactions than that in which the form can include based on current count of rows included. The number of transactions however could fluctuate. There can and will be a multitude of carriers that will be included in each form. The way we have our workbook now, each form is located in its own worksheet and nearly all data entry has 75%+ manual input. I have pondered whether it would be better to have all the forms in one sheet to auto populate then print or not. I am open to all ideas, as well as fully making the effort to understand what is suggested as well so that I can further my updating of other forms and schedules as well.

[Leith Ross]

Hello Chris,

After I did a little more testing on the workbook I just posted, the Form worksheet needs to be visible and there is correction to the macro code as well. I forgot to add the minus sign to the cell offsets. Anyway, try this version and let me know how this fits your needs.

Please Login or Register  to view this content.

[chrisTXACC]

Leith,

Thank you so much. This is a wonderful macro. In addition to the creation of new sheets operating from the template form; is it possible to have the new sheets which are titled from the destination description actually only be titled to the last four digits of each description? Also, is it possible to have the imported transactions be sorted by date once in the new form?

[Leith Ross]

Hello Chris,

No problem. I start on making the changes.

[Leith Ross]

Hello Chris,

Here is the updated macro and workbook.

Please Login or Register  to view this content.

[chrisTXACC]

Leith,

This really has been more successful than I could have imagined. What are your thoughts on this potential modification: a second macro that will pull together all the spreadsheets to print to a pdf file? Or export to individual pdfs for each spreadsheet to a new created folder within the same location of the workbook itself?

Additionally, is it possible to modify the code that in the event there are more lines of transactions than the template form permits, a second form is created to accommodate the additional transactions?

Finally, a simple thing I forgot to mention, is it possible to add a line that sorts the raw date in order by "destinationdescription" prior to the rest of the macros processes in order to have all the spreadsheets organized in numerical order once created?

Thanks for your generosity in developing and modifying this to better suit my application!

[Leith Ross]

Hello Chris,

This will do everything you asked except extend the "Form". You can add a new file name to replace the file name I created for the PDF file. The file name is "Exported Data yyyy-mm-dd.pdf" and is saved in the same folder as the workbook. There are only 2 macros: ExtractToForms_3 and SaveAsPDF.

The choice to save all the forms in a single PDF or individual PDF files in folder depends on how you need to access the information later on. If the data needs to be retrieved on a weekly, or monthly basis then using a single PDF would work. If you need to access more specific information about individual forms then you should probably save the individual forms as PDF files.

Macro to Create Forms from Raw Data

Please Login or Register  to view this content.

Macro to Save Worksheets As A PDF

Please Login or Register  to view this content.

Bliadhna Mhath Ùr agus a h-uile nì nas fhearr na chéile!

[chrisTXACC]

This is has proven very useful. However, for the print to pdf macro, I am facing a complication. Attached is the screen capture of the debugger window.

Capture.PNG

EDIT (Additional error):

I attempted to rerun the Create Forms Macro with new raw data and I am getting another error. "Run-time error '9': Subscript out of range"

Capture1.PNG

[Leith Ross]

Hello Chris,

The first error is a mystery. I cannot see a logical reason for the error. What is error number for this error?

The second error tells me the data layout has changed. Can you post a copy of the new data?

[chrisTXACC]

For the first issue see the following: Capture.PNG

As far as the second issue goes, It is working at the moment, once I had filtered some things out of it. However, the formatting did not change which makes me wonder if there was something very specific causing the error as the only thing changed in the cells were their values.

[Leith Ross]

Hello Chris,

To get "Method of 'Range' object failed" indicates an issue with the VBA library. This should not happen because the VBA library is loaded. This type of problem indicates your Windows Registry needs to be defragmented. If you don't own a registry deframentation program, buy one. It seems that doing a lot of VBA development work wreaks havoc on the registry. I have a program I use every week and rarely have issues anymore with Excel or other applications I am developing code for.