+ Reply to Thread
Results 1 to 8 of 8

Need Help! Need button to copy entire table data and paste in last row of another sheet

  1. #1
    Registered User
    Join Date
    03-21-2016
    Location
    Lenexa,KS
    MS-Off Ver
    2016
    Posts
    4

    Post Need Help! Need button to copy entire table data and paste in last row of another sheet

    I really need some help here, i am working on this excel workbook for work and want to make a button that when pushed, it will copy the data from the table on one sheet to another sheets archive, which it will then take all the data and disperse appropriately across the other tabs to fill in the respective forms. I have 5 sheets, one being a table with
    (QTY) (ID) (PO) (Date) Promise By (L) (UOM) (W) (UOM) (Description)
    300 1320110 2020182 3/14/16 3/21/16 3 mm x 965 mm Long O Ring Blue Textured
    12 1804549 2020182 3/14/16 3/21/16 T5/2190/50 Lat rid backing, 10 mm grove center bottom
    40 1805299 2020182 3/14/16 3/21/16 4 mm x 1075 mm Long O Ring
    10 1805888 2020182 3/14/16 3/21/16 Long O ring
    6 18060202 2020182 3/14/16 3/21/16 T05/2120/068 No Groove for now. Spike top back.
    2 18070081 2020182 3/14/16 3/21/16
    1 18021035 2020182 3/14/16 3/15/16 4.88 '' x 266.53 '' nel Blue Neg PYR w/ v-guide off set 75mm
    1 18020723 2020182 3/14/16 3/15/16 6.81 ''x 325.59 '' NEL Blue Neg Pyr V-Guide
    and 3 other individual ones that take the same table and auto fill forms to be printed. The table also needs to be inserted to the top of the sheet to then add to archive. I have read through all these fourms and cant find what i need and or not able to modify the code to work for me. This is what i got so far
    Sub AddTextButton()
    Dim Lrow As Single

    With Worksheets("New Shop ORder")
    Lrow = .Range("B" & Rows.Count).End(xlUp).Row + 1
    .Range("B" & Lrow & ":P" & Lrow) = .Range("A2:P2").Value
    .Range("B4:P4").Value = ""
    End With

    End Sub

    But it only copies a single row, and i am not able to get it to copy to a different sheet, PLEASE HELP! I would greatly appreciate it!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,926

    Re: Need Help! Need button to copy entire table data and paste in last row of another shee

    Could you provide a sample workbook. It will make it easier to visualize the problem. It sounds like what you need to do is work with *real* Excel tables. The code you provided looks like it's working with a range. Also us code tags in your post, it makes it easier to read.

    Also when you copy to "another sheet," do you want to overwrite the data there or do you want to append to the data there.

    What does it mean to "take all the data and disperse appropriately across the other tabs to fill in the respective forms." Could you elaborate? Again a sample workbook would help a lot.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,926

    Re: Need Help! Need button to copy entire table data and paste in last row of another shee

    Could you provide a sample workbook. It will make it easier to visualize the problem. It sounds like what you need to do is work with *real* Excel tables. The code you provided looks like it's working with a range. Also us code tags in your post, it makes it easier to read.

    Also when you copy to "another sheet," do you want to overwrite the data there or do you want to append to the data there.

    What does it mean to "take all the data and disperse appropriately across the other tabs to fill in the respective forms." Could you elaborate? Again a sample workbook would help a lot.

  4. #4
    Registered User
    Join Date
    03-21-2016
    Location
    Lenexa,KS
    MS-Off Ver
    2016
    Posts
    4

    Re: Need Help! Need button to copy entire table data and paste in last row of another shee

    I have provided a sample workbook as requested, Thank you so so much for the help!! I am a newby at working with Macros but see how helpful it can be so i really appreciate the help teaching me. I attached the workbook i am working with but removed the macros i intially had to make the size smaller. I have 11 Sheets in total that I want to be filled with data from a single sheet. Let me explain.

    When we receive a PO I go to a different workbook that has all my products, pricing and details for manufacturing. I copy the rows i need, and paste into this attached workbook " Master Shop File" on sheet 2 "Order-Archive". On the Sheet "Order Archive" i then clear my revenue values, and leave the details needed for the shop to create the belts, and then i send down to the shop. This has been causing a few routine problems of mis typing or mixing numbers, which is costing me, and which is why i am here now, asking for help.

    I currently do the following process for new orders. Step 1 i copy the products data into this workbook, Step 2 I use shortcut Command+Option+T to clear columns of revenue data E-I, Step 3 I copy the set 22 row template (Standard for every order) from Sheet "Order-Archive" into the top of the next Sheet "ShopWO", This then fills in the packing slip template, and Shop Work Order template i have below it by referencing the cells. Step 4 is for the shop supervisor to print the packing slip and shop WO, and the individual Index Cards to follow the product being manufactured in the shop.

    This is where i begin to need help. I want to be able to copy my data into this workbook 1 time, and for it to fill the other sheets from that single paste. Whatever is easiest to code i will use as i am very flexible on this. I then want the Shop WO pages that will be printed to be archived somehow into a different sheet or below them so i can keep a log, if possible.

    Then on the Index Cards page, this needs some help too. The Rows of data on top don't really work for this. I need a way to print the index card onto 4x6 label, for each line item. On-top of that, IF, the category of product is Conveyor Belt, or 2PC Timing Belt, i need to print however many QTY was ordered of that line item. So for example, Someone orders 3 - conveyor belts, 15 - 3mm O-ring, and 6 - T05/2120/068 Timing Belts. So for the 3 conveyor belts I will need the row of data to fill the card , and print 3 copies. The O-rings i will need the row of data to fill the card and print 1 for the 15 ordered, because its all at one station. the Timing Belts i will need the row of data to fill the card and print 6 copies. These do not need to be archived, they are simply for purposes of QC, but i would like a way of knowing what cards are still out there, and which have been received if possible. The other sheets simply can not be automated due to the nature of business. The Dashboard page i want to make a quick-view as to what orders are open, and have not been shipped. With the orders promised date, and original placement of order next to it for my shop to see as a heads up display.

    Anything at all you can help me with would be greatly appreciated! Thank you so so so much! I promise to return the favor in any way i can!
    Attached Files Attached Files

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,926

    Re: Need Help! Need button to copy entire table data and paste in last row of another shee

    I am unable to download the workbook. Please try attaching it again. This site is an excellent resource but still has a couple of bugs.

  6. #6
    Registered User
    Join Date
    03-21-2016
    Location
    Lenexa,KS
    MS-Off Ver
    2016
    Posts
    4

    Re: Need Help! Need button to copy entire table data and paste in last row of another shee

    http://www.excelforum.com/attachment...1&d=1460138777

    I think i have reattached it now? Im rather confused on how to attach, i went to the attachment manager and uploaded but dont have a choice to select in this reply box under the attach option

    Thanks again!!
    Attached Files Attached Files
    Last edited by JETTLIFETech; 04-08-2016 at 02:16 PM. Reason: forgot thank you

  7. #7
    Registered User
    Join Date
    03-21-2016
    Location
    Lenexa,KS
    MS-Off Ver
    2016
    Posts
    4

    Re: Need Help! Need button to copy entire table data and paste in last row of another shee

    ?? Figure out anything ?

    we have kind of made a work around by defining a name for the table and referencing that in the macro.

    Now i need to append a letter to the packing slip every time a shipment is sent out

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,926

    Re: Need Help! Need button to copy entire table data and paste in last row of another shee

    Are you sure you attached the correct file? The tabs don't seem to match the descriptions in your post.

+ 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. Copy paste entire row in another sheet having xyz name
    By watermasa in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-24-2015, 03:22 AM
  2. [SOLVED] VBA Code to copy all data from sheet 1 and paste it to sheet5 using a command button
    By Angnz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2013, 10:06 PM
  3. [SOLVED] VBA code to copy and paste an entire row from one sheet to another
    By emach in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-24-2013, 12:12 PM
  4. Replies: 0
    Last Post: 05-21-2013, 03:09 PM
  5. to copy paste the entire row to the new sheet
    By karanpreet in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-15-2013, 10:51 AM
  6. Help with Copy entire row and paste to new sheet
    By 20lgt06 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2013, 09:24 AM
  7. [SOLVED] Loop through entire sheet, Insert row, copy row data, paste row data, delete row
    By Hyperdude in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2012, 01:46 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