Forum Discussion
Export row to multiple sheets
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
- May 09, 2018
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.
- Matt MickleMay 09, 2018Bronze Contributor
Cory-
I agree with Inge on this one. A better structured process would be ideal. Not sure if you'll be able to accomplish that or not so here is some code that may get you by in the meantime. In this example I've made some sample data based on the information you provided. I named this file Building Base File.xlsm. I saved this file in a folder on my desktop called Building Files. I also put the below code in a new code module (I commented the code so it will be a little easier to comprehend the purpose of each line/section). In order to put the code in the workbook you can:
1. Use Key combination Alt + F11 to access the Visual Basic Editor (VBE)
2. On the top ribbon > Insert Module
3. Paste the below code in the code module that appears
Option Explicit Sub MoveBuildingData() 'Declarations Dim SrcBk As Workbook Dim NewBk As Workbook Dim srcSht As Worksheet Dim Lrow As Long Dim LngLp As Long 'Turn off screen updating Application.ScreenUpdating = False 'Define Source Workbook and Source Worksheet Set SrcBk = ThisWorkbook Set srcSht = SrcBk.Sheets("Sheet1") 'Change to fit your needs 'Define Last Row based on column A Lrow = srcSht.Cells(Rows.Count, "A").End(xlUp).Row 'Loop through data row 2 to the last row For LngLp = 2 To Lrow Set NewBk = Workbooks.Add 'Create New Workbook... 'Transfer data to new workbook With NewBk.ActiveSheet .Range("A1") = srcSht.Range("A" & LngLp) .Range("A2") = srcSht.Range("B" & LngLp) .Range("A4") = srcSht.Range("C" & LngLp) .Range("C4") = srcSht.Range("E" & LngLp) End With 'Save the new workbook in the same file folder as the data file NewBk.SaveAs Filename:=SrcBk.Path & "\" & srcSht.Range("A" & LngLp) & " - " & Format(Date, "mm-dd-yyyy") & ".xlsx" NewBk.Close 'Close workbook Next LngLp
'Turn on screenupdating
Application.ScreenUpdating = True End Sub4. When you're ready to run the code. You can hit the green "play button" on the VBE ribbon or hit F5.
Here's a screenshot of the before and after to expect based on the above code. Please feel free to ask additional questions if I have been unclear. Always happy to help.