+ Reply to Thread
Results 1 to 20 of 20

Deleting Rows and Transferring Data into New Worksheet Template

  1. #1
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Deleting Rows and Transferring Data into New Worksheet Template

    Hey everyone,

    I just some code inquiries concerning a macro I am trying to create. First off, I think it would be helpful if I gave a little background of what I am trying to do. I have created an excel document that holds the information of many of the employees in my department. I want to copy any row that contains data in the "Task" & the "Quarter for 12/1 - 2/28" columns (the G & I columns). Then, I want to paste these rows into a New Worksheet. I have had some luck thus far (as you can see in the below code). However, as you can see my code is copying everything (rather than referring to the select columns) and it is not generating a new worksheet from scratch, rather it is pasting it into an already open worksheet. Also, (this seems tricky to me) would it be possible if I could make this macro create a new column after column L that says "Resource Hours Cumulative" within the new worksheet.

    If anyone could help me out I would greatly appreciate it.

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    First off, can you provide a sample workbook with fake data that I can manipulate as needed? I believe I have a script to fit your needs. I would just like to test it before I say for sure.

    Secondly, here is a little more compact version of your code:
    Please Login or Register  to view this content.
    Last edited by Xer2; 07-09-2012 at 01:54 PM.

  3. #3
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Sure, if you need anything else please let me know!
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    I think this does what you need. Let me know if it doesn't, or if there is anything else you need.

    Please Login or Register  to view this content.
    Last edited by Xer2; 07-09-2012 at 04:16 PM. Reason: Updated Code

  5. #5
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Thank you so much Xer2. It works great! Would you happen to know why the values under the J Column aren't being transferred over into the new sheet?

    My purpose is to SUM columns I and J. The addition sub procedure I created seems fine but as I mentioned before there are no values under column J. Finally, is it possible for me to create a sub procedure that will do a "quality check" on the Updated Info sheet where I make sure that columns B, C, D, E, M, N, O columns are filled out.

    I know I am asking for a lot so if it is too much let me know.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    I'm not sure why it is skipping "J". I will look into it.

  7. #7
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Great thanks! I'm actually having a little more trouble with the code. For some reason there are multiple duplicates generated in the new "Updated Info Sheet."

    Also, Would you also happen to know why the code below doesn't work.

    Please Login or Register  to view this content.
    Then

    I added the wSheet.Range("G") because I wanted multiple criteria before copying the cells into the appropriate "Updated Info" sheet.

    For some reason, besides the J column not being copied over, anything that is designated as a new task within the new worksheets isn't being copied over as well!

    Thank you for your time. I really appreciate it.

  8. #8
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Nevermind about the duplicate rows, for some reason the people who filled it out had multiple instances of the same task..

  9. #9
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Try this code instead of the other.

    Please Login or Register  to view this content.
    Still working on the formatting for blank cells macro. It is causing some errors right now.

  10. #10
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Thanks Xfer you're a life saver. If it helps it seems like the code from before is copying the same data from the old data from the ResourceHours sheet with a filter under Tasks that removes blanks.

  11. #11
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Additionally, I'm getting a type mismatch error with the following:

    Please Login or Register  to view this content.
    I will try to figure it out as well in the mean time.

  12. #12
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    On the "AXA BS - GA" tab in cell J31, there is a - instead of having a number or being blank. Clear cell J31 and try again.

  13. #13
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    I'm sorry to bother you Xfer but would you happen to know why my values from "Discretionary / KTLO / Service / OPS" column are getting cut off when being copied into the new "Updated" sheet?

  14. #14
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Yes I do, I need to fix part of the loop. Sorry about that. The update will be up soon.

  15. #15
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Try this one:

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    No, it doesn't work =(

  17. #17
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Sorry about all this. Try this one:

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    No need to apologize! You're helping me out! Thank you again Xfer =)

  19. #19
    Forum Contributor
    Join Date
    07-05-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2016
    Posts
    165

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Did that one work for you?

  20. #20
    Registered User
    Join Date
    06-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Deleting Rows and Transferring Data into New Worksheet Template

    Yes it did, it was perfect. Thank you again for your time and patience.

+ 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