Forum Discussion
Export row to multiple sheets
To my knowledge there is no standard method for separating out one row of data into many different workbooks. You would need to use a macro (VBA) to accomplish this task. Writing the code for this is fairly simple, but I see you are referencing a form. What type of form are you trying to fill in? A web form? An Excel userform? A worksheet form? The more detailed information you can provide the better possibility you will get a favorable response from the community. Can you please provide a non-sensitive example file or a few screenshots to further illustrate your issue?
Hello Matt, thank you for the reply.
I used the word "form," but I suppose I should just say that I need to export it to another sheet. If I have the data in one sheet like this...
1: Building-A: Location-A: Data-D: Data-E: Data-F
2: Building-B: Location-B: Data-D: Data-E: Data-F
3: Building-C: Location-C: Data-D: Data-E: Data-F
I would like to create three separate sheets that look something like this.
Building-A
Location-A
Data-D: Data-F
Then input boxes for a different user to fill in.
Once I see the code for moving the data from one row to another sheet, then I can probably figure out the rest. Notice that I didn't include Data-E on the new sheet. I don't need all of the information from each row.
Again, I appreciate the assistance.
Cory
Hello,
I'm sure you describe what you want to achieve and it makes sense to you, but it is probably not the best information architecture for efficient workbook design. Hundreds of sheets sounds like a bad plan.
If you have all the core data on one sheet, you have the perfect starting point. It would make a lot more sense to create just one new sheet with a form. Give the user a control to select which row of data they want to edit and bring up that data in the sheet (form). Let the user fill in the fields and use VBA to write the data to the corresponding row in the initial sheet.
Excel can be very dynamic. It is more efficient to create ONE sheet and load the data from the data table, than creating hundreds of sheets that all look the same. If you spread the data over many sheets, you will have a lot of difficulty reporting on the data, but if it all stays in one sheet, reporting will be a breeze.
Hello Ingeborg, and thank you for the reply.
Unfortunately, it seems that I am doing a fairly poor job at communicating what I need to accomplish. Let me try again, and hopefully it will be clear. I'll use generic terms for sensitivity, of course, but this is the jest of what I need.
I have multiple Excel docs, each containing about 10 to 20 rows of information. Overall I have hundreds, but not in one document. Each document represents a building owner. The rows represent work orders, and each column is specific information related to those work orders, like order number, date, description, etc.
I need to make a system where I can print specific information from each work order individually, and send them one at a time to the building owner. Yes, it's terribly inefficient, but it's what I must do. My idea is to have a nice clean printed piece of paper with all the information filled out, except a few blank boxes for them to fill back in. I don't need to re-input this later, so there is no need to maintain the Excel database beyond the print.
I hope this clarifies what I am looking for. Thank you all again for your assistance.
Cory
Hello,
this is getting even better. With multiple workbooks that all have the same structure, you can use Power Query (called Get & Transform in Excel 2016) and combine the data of all the workbooks in one master workbook without writing a formula or writing code.
Power Query can be configured to consider new data files, new files in a folder hierarchy and many other ways to ensure that new data is included.
In the master book that reads (read only, it does not write anything back) the data from all those workbooks, you can create a special sheet where you use cell inputs to determine which Owner and/or which work order is being displayed on the special display sheet. Just ONE display sheet.
You can then fill in other stuff on that display sheet and click a button that uses VBA to send a copy of JUST THAT SHEET via email to the desired other party.
This is a combination of VBA code to copy and email a sheet, and Power Query to pull data from various sources.
Using Excel, it still looks and feels a bit bumpy and pedestrian, but it can be done. The more efficient way would be to use a database like Access or SQL to store the data and build the forms and the business logic in the database.
I strongly encourage you to try out Power Query/Get & Transform to consolidate all the building owner files into one workbook. It's not really all that hard to do. This Microsoft Support Article is a good starting point.
Power Query has its own learning curve but it also has a great user community with lots of resources and volunteer helpers.
Let me know if you need more help.
