+ Reply to Thread
Results 1 to 14 of 14

Distribute data from Single entry table to Multiple Workbooks

  1. #1
    Registered User
    Join Date
    12-01-2016
    Location
    uae
    MS-Off Ver
    2010
    Posts
    7

    Distribute data from Single entry table to Multiple Workbooks

    I have created a workbook for asset history where each asset has its own worksheet which needs to be continually updated as assets are utilized.
    For of data entry I would like to have a single input table where usage and data on multiple assets can be entered at one time . I am looking for a method send the data including header data to the specific asset worksheet

    The worksheet name will be the asset id


    Input Table.PNG

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Distribute data from Single entry table to Multiple Workbooks

    It would be easier to help if you could post a copy of your file (not a png) that contains samples of your input sheet and asset sheets. Include a detailed explanation using a few examples and referring to specific cells and sheets to describe what data you want copied and where you want to paste it. De-sensitize the data if necessary.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    12-01-2016
    Location
    uae
    MS-Off Ver
    2010
    Posts
    7

    Re: Distribute data from Single entry table to Multiple Workbooks

    Guess the worksheet would be helpful

    Attached is the frame work of what I am looking to do. The primary sheets are being used are the Input Sheet and the Asset sheet (97545). I would like to be able to put information on multiple assets at one time in the input table and have that information moved over to the sheet nominated by the Asset ID
    Attached Files Attached Files

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Distribute data from Single entry table to Multiple Workbooks

    Try:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-01-2016
    Location
    uae
    MS-Off Ver
    2010
    Posts
    7

    Re: Distribute data from Single entry table to Multiple Workbooks

    Mumps1

    Thanks, this is what I need. I have put the code in however and noticed that instead of going to the next row when entering a new batch of data it overwrites

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Distribute data from Single entry table to Multiple Workbooks

    I'm not sure how that is happening. Each time you run the macro, the data in each asset sheet is cleared and then all data, including any additions, is copied back to the asset sheet so that each time the macro is run, you should have all data up to date. If this is not happening, post your file and explain in detail how it is not working for you.

  7. #7
    Registered User
    Join Date
    12-01-2016
    Location
    uae
    MS-Off Ver
    2010
    Posts
    7

    Re: Distribute data from Single entry table to Multiple Workbooks

    Mumps1

    Attached is the sheet with the code. it did not appear to trigger automatically so placed it under a command button (Button 1). Could this be the culprit
    Attached Files Attached Files

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Distribute data from Single entry table to Multiple Workbooks

    Try:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-01-2016
    Location
    uae
    MS-Off Ver
    2010
    Posts
    7

    Re: Distribute data from Single entry table to Multiple Workbooks

    Mumps1

    The second code gave the same results. With a bit of trial and error, both versions you send appear to work if the following lines are removed

    Sheets(CStr(ID.Value)).Range("B11:L" & LastRow2).ClearContents
    Sheets(CStr(ID.Value)).Range("N13:U" & LastRow2).ClearContents

    Any reason why?

    With the following
    Sheets(CStr(ID.Value)).Cells(Rows.Count, "N").End(xlUp).Offset(1, 0) = Sheets("Input").Range("E2")
    Sheets(CStr(ID.Value)).Cells(Rows.Count, "O").End(xlUp).Offset(1, 0) = Sheets("Input").Range("I5")

    There will not always be a comment so I would like to not fill either the date or comment if the comment (Input Page) is empty. I played with it and can make it work for the first line in the input however if you put a comment for the second asset it will not copy either

    Thanks for your assistance
    Last edited by noront; 12-05-2016 at 07:10 AM.

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Distribute data from Single entry table to Multiple Workbooks

    My apologies. In my hurry, I forgot to mention that I had to revise your file to remove some merged cells that were creating some issues. You should try to avoid using merged cells if possible becaue they almost always give Excel problems. Try the attached file.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-01-2016
    Location
    uae
    MS-Off Ver
    2010
    Posts
    7

    Re: Distribute data from Single entry table to Multiple Workbooks

    Mumps1

    Had a bit of an epiphany with the file you sent yesterday, thanks. I made an couple changes and it seems to be working. I am triggering this with a command button and occasionally it will only copy the job number to one sheet. When this happens the command button resets quickly where as in normally is depressed until the script is complete.

    Please Login or Register  to view this content.
    All left to do is figure out how to delete cells after input

    Thanks

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Distribute data from Single entry table to Multiple Workbooks

    You removed the code that clears the sheets of data before copying the data over. This means that each time your run the macro, you will end up with duplicate values in your destination sheets. It should work properly with the 'ClearContents" part of the code.

  13. #13
    Registered User
    Join Date
    12-01-2016
    Location
    uae
    MS-Off Ver
    2010
    Posts
    7

    Re: Distribute data from Single entry table to Multiple Workbooks

    When I left the clear contents line in
    Please Login or Register  to view this content.
    it would not write beyond the B11 and N12 limits. Changed those to B65000 and N65000 and the problem was resolved. I hope I did not make the lines irrelevant with this?

  14. #14
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Distribute data from Single entry table to Multiple Workbooks

    It's difficult to see what is going on without seeing your file. If your changes work for you, then that's what counts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-15-2013, 08:50 AM
  2. Replies: 3
    Last Post: 01-22-2013, 03:22 PM
  3. Single Data Entry for Multiple Cells
    By awoods_5 in forum Excel General
    Replies: 1
    Last Post: 12-26-2012, 09:58 AM
  4. [SOLVED] Distribute the data from Single Column to multiple Columns
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-29-2012, 02:11 PM
  5. Importing data from multiple workbooks into a single spreadsheet
    By kflotlin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2012, 11:52 AM
  6. consolidate data from multiple workbooks to a single worksheet
    By vbadummy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2010, 02:42 PM
  7. distribute data in single sheet to multiple sheets
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-23-2009, 08:33 PM
  8. Sort Data on Multiple sheets by single cell Entry
    By Zimbo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2008, 03:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1