Export row to multiple sheets

Question

Hello,&nbsp;I'm pretty new to the world of Excel. I'm trying to figure out how to do the following and I'm hoping someone can assist me.I have one sheet that contains hundreds of rows of data. The rows are compiled like this.&nbsp;Building #: Address: Type: Info A: Info B: Info C: etc:&nbsp;I need to take every row and export it into its own sheet, or separate file. Then on that separate file or sheet, I need to have it fill out a form automatically. Is there a standard method for performing this operation? I appreciate any assistance.&nbsp;Thank you,Cory&nbsp;&nbsp;

matt mickle · Answer

To my knowledge there is no standard method for separating out one row of data into many different workbooks.&nbsp; You would need to use a macro (VBA) to accomplish this task.&nbsp; Writing the code for this is fairly simple, but I see you are referencing a form.&nbsp; What type of form are you trying to fill in?&nbsp; A web form?&nbsp; An Excel userform?&nbsp; A worksheet form?&nbsp; The more detailed information you can provide the better possibility you will get a favorable response from the community.&nbsp; Can you please provide a non-sensitive example file or a few screenshots to further illustrate your issue?

cory mckissick · Answer

Hello Matt, thank you for the reply.&nbsp;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...&nbsp;1:&nbsp; Building-A:&nbsp; &nbsp;Location-A:&nbsp; &nbsp; Data-D:&nbsp; &nbsp; &nbsp;Data-E:&nbsp; &nbsp; &nbsp;Data-F2:&nbsp; Building-B:&nbsp; &nbsp;Location-B:&nbsp; &nbsp; Data-D:&nbsp; &nbsp; &nbsp;Data-E:&nbsp; &nbsp; &nbsp;Data-F3:&nbsp; Building-C:&nbsp; &nbsp;Location-C:&nbsp; &nbsp; Data-D:&nbsp; &nbsp; &nbsp;Data-E:&nbsp; &nbsp; &nbsp;Data-F&nbsp;I would like to create three separate sheets that look something like this.&nbsp;Building-ALocation-A&nbsp;Data-D:&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;Data-FThen input boxes for a different user to fill in.&nbsp;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.&nbsp;&nbsp;Again, I appreciate the assistance.Cory&nbsp;&nbsp;

ingeborghawighorst · Answer

Hello,
&nbsp;
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.
&nbsp;
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.
&nbsp;
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.

cory mckissick · Answer

Hello Ingeborg, and thank you for the reply.&nbsp;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.&nbsp;&nbsp;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&nbsp;specific information related to those work orders, like order number, date, description, etc.&nbsp;&nbsp;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.&nbsp;&nbsp;I hope this clarifies what I am looking for. Thank you all again for your assistance.Cory

matt mickle · Answer

Cory-
&nbsp;
I agree with Inge on this one.&nbsp; A better structured process would be ideal.&nbsp; 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.&nbsp; In this example I've made some sample data based on the information you provided.&nbsp; I named this file Building Base File.xlsm.&nbsp; I saved this file in a folder on my desktop called Building Files.&nbsp; 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).&nbsp; In order to put the code in the workbook you can:
&nbsp;
1. Use Key combination Alt + F11 to access the Visual Basic Editor (VBE)
2. On the top ribbon &gt; Insert Module
3. Paste the below code in the code module that appears
&nbsp;
&nbsp;
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" &amp; LngLp)
            .Range("A2") = srcSht.Range("B" &amp; LngLp)
            .Range("A4") = srcSht.Range("C" &amp; LngLp)
            .Range("C4") = srcSht.Range("E" &amp; LngLp)
        End With
        'Save the new workbook in the same file folder as the data file
        NewBk.SaveAs Filename:=SrcBk.Path &amp; "\" &amp; srcSht.Range("A" &amp; LngLp) &amp; " - " &amp; Format(Date, "mm-dd-yyyy") &amp; ".xlsx"
        NewBk.Close 'Close workbook
    Next LngLp    'Turn on screenupdating    Application.ScreenUpdating = True

End Sub

&nbsp;
4. When you're ready to run the code.&nbsp; You can hit the green "play button" on the VBE ribbon or hit F5.
&nbsp;
Here's a screenshot of the before and after to expect based on the above code.&nbsp; Please feel free to ask additional questions if I have been unclear.&nbsp; Always happy to help.
&nbsp;

&nbsp;
&nbsp;

Forum Discussion

Export row to multiple sheets

6 Replies

Resources