+ Reply to Thread
Results 1 to 14 of 14

VBA- Copy & Paste Rows Into Different Worksheets

  1. #1
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Unhappy VBA- Copy & Paste Rows Into Different Worksheets

    Hello,

    I am hoping that someone out there can assist me with my project, because I have never done this, I have read all about macros so understand the concepts but am getting frustrated.

    I have been given a worksheet that I need to create a macros for. What needs to be done is this macros needs to filter the word in column A, "North" and then filter again in column B for "HCF" (each row contains information in columns a-g). Once that has been filtered, it has to copy those rows that were filtered, go to the worksheet labelled "North", navigate to the row named "HCF", and paste it to the next available blank row below that header. Then navigate back to the "Master" worksheet and deselect the "autofilter" function, so that the master copy looks untouched.

    just for additional info, this workbook contains 4 worksheets labelled: master, north, south, and east.

    and then in north, south, and east there are at least 4 sections that they are divided into "HCF, HDA, ADU" etc. and the information from the master copy needs to be divided by their unit (North,South, or East) and then subunit "HDA,HCF" for example.

    hope this makes sense and that if someone can provide me a code for the first one that i can figure out the rest, i hope!

    thanks alot for who reply to this message.
    Last edited by NewbieOfVBA; 07-03-2012 at 02:24 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Need To Create A Macros That...

    I can't help you enough with VBA.

    I'm interested in what you're gonna do with the 4 worksheets.

    If you gonna count with it, maybe you better make a master sheet and use a pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Need To Create A Macros That...

    so does that mean you can't help at all, or you need additional information? the 4 worksheets are just there to divide the information up. the user will input the information to the "master" worksheet. and then when they run the macros it will divide that information by worksheet unit name "north, south or east" and then into subunit "HDA, HCF etc."

    thanks

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need To Create A Macros That...

    NewbieOfVBA,

    Welcome to the forum!
    Give this a try:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Need To Create A Macros That...

    That looks intense and wonderful at the same time, wow I wish I was as fast as you are!

    I tried to run this macros and it filtered "north, HCF" copied those rows to the correct worksheet and header, but then came up with "run-time error '91':" object variable or with block variable not set". I am just curious though. where it says in the code "for each subunit in split" Do I add each subunit to there for each worksheet? or do i add them all there no matter what worksheet they're in..

    i'm still shocked at how fast you did that haha

    thanks!

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need To Create A Macros That...

    NewbieOfVBA,

    Oops, didn't put in a check to make sure rngDest exists or not, which is likely what caused the error. You would put all of the subunits in the Split line, separated by a comma as shown. Here's the updated code:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Need To Create A Macros That...

    Oh my god you are genius!

    haha ok, so it works but for some reason it copies the 'North HCF' and pastes it twice. otherwise it works beautifully

    wow so amazed

    thanks

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need To Create A Macros That...

    The macro is designed to put data it finds underneath existing data. So if the macro runs, it gets the info and puts it in the proper place. Then, when run again, it gets the info and puts it underneath the previous info it found, which is likely why it copied 'North HCF' twice.

    Would you prefer it overwrite the existing data instead?

  9. #9
    Registered User
    Join Date
    06-30-2012
    Location
    İstanbul
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Need To Create A Macros That...

    www.matistvba.com FOR BEGINNERS

  10. #10
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Need To Create A Macros That...

    hello. i think i understand what you are asking. if the macros was run twice it would replace the previous data with the new data (even though it may be the same information), as long as i do not get duplicate rows of the same information i am happy. overall this macros will be run once monthly with new information added each time and the previous information will still be there. So as the master copy is added to, the macros will be added to as well. hope that makes sense.

    thanks!

  11. #11
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: VBA- Copy & Paste Rows Into Different Worksheets

    This version will override existing data:
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: VBA- Copy & Paste Rows Into Different Worksheets

    thank you so much you are a huge help!

    it works awesome but as I can see, macros requires every specific detail to be included. So my issue now is that underneath the sub-headings (HDA,HCF etc.) I have labeled the columns to indicate what is in each row, they are as follows : Process Unit, Unit, Equipment #, Scheduled Start Date, Work Order #, Description, Cost), when i run the macros these disappear and the information replaces it. can you implement into this macro so that these headings do not get replaced?

  13. #13
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: VBA- Copy & Paste Rows Into Different Worksheets

    [QUOTE=NewbieOfVBA;2846457]thank you so much you are a huge help!

    it works awesome but as I can see, macros requires every specific detail to be included. So my issue now is that underneath the sub-headings (HDA,HCF etc.) I have labeled the columns to indicate what is in each row, they are as follows : Process Unit, Unit, Equipment #, Scheduled Start Date, Work Order #, Description, Cost), when i run the macros these disappear and the information replaces it. can you implement into this macro so that these headings do not get replaced?[/QUOTE

    disregard this message, i have figured it out! thanks

  14. #14
    Registered User
    Join Date
    06-29-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: VBA- Copy & Paste Rows Into Different Worksheets

    So I figured out the other problem in reference to not replacing headings. But now i have a new one.
    Can you re-write this code so that the macros adds the information without replacing the next sub-unit if there aren't enough rows between each. So if it is pasting 14 rows under the sub-unit "EB" and there are only 12 rows between "EB" and "SM" it will add that information without copying over "sm". As this will become a very large a library and new rows will constantly be required.
    Also, i need it to not delete old entries that have been deleted off the master. I will be entering in new information and deleting the items I have already entered previously, and then running the macros. I need that previous information to not be replaced when the macro is run again, just need it to be built upon.

    thanks
    Last edited by NewbieOfVBA; 07-04-2012 at 06:04 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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