+ Reply to Thread
Results 1 to 13 of 13

Paste data from One Sheet to Another

  1. 05-08-2013, 12:33 PM #1
    Mart1618
    Mart1618 is offline
    Registered User
    Join Date
    05-03-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Paste data from One Sheet to Another

    Hi All:

    I'm trying to copy data from one sheet to another. The following code is working but the problem is that it only copies the first line and it does not select any other breached or warning ticket(see attachment) If you open the sheet the code won't work because I set the Range to "D7:AC7" (See the commented lines below):

    Please Login or Register  to view this content.
    Can anyone help solve this issue, please?

    Thanks to AB33 I was able to get the code working see below:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Mart1618; 05-09-2013 at 08:27 AM. Reason: Final Code
    Reply With QuoteRegister To Reply
  2. 05-08-2013, 12:50 PM #2
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Paste data from One Sheet to Another

    How about this line?

    Please Login or Register  to view this content.
    is the part that is not working properly if I change this to "D7:AF7" it works but it only grabs the first Breached ticket

    But you need to move the LR above this line
    Reply With QuoteRegister To Reply
  3. 05-08-2013, 01:02 PM #3
    Mart1618
    Mart1618 is offline
    Registered User
    Join Date
    05-03-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Paste data from One Sheet to Another

    That fixes the error but it displays all the content. For some reason the criteria 1 and 2 are not working properly.
    Reply With QuoteRegister To Reply
  4. 05-08-2013, 01:47 PM #4
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Paste data from One Sheet to Another

    If you look at column AC, the filter selects both criteria. What is your expectation?
    Reply With QuoteRegister To Reply
  5. 05-08-2013, 01:58 PM #5
    Mart1618
    Mart1618 is offline
    Registered User
    Join Date
    05-03-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Paste data from One Sheet to Another

    The filter selects all the content. If I erease the criteria nothing happens everything gests copied to the sheet. If I add the criteria it does the same thing. I want the code to only display the breached and Warning the ones that do not have B or W should not be copied. A user was able to do that but with a different example see the link http://www.excelforum.com/excel-gene...her-sheet.html
    Reply With QuoteRegister To Reply
  6. 05-08-2013, 02:21 PM #6
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Paste data from One Sheet to Another

    I think the autofilter field is wrong, it should be 26
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 05-08-2013, 02:37 PM #7
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Paste data from One Sheet to Another

    Try this line

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 05-08-2013, 02:58 PM #8
    Mart1618
    Mart1618 is offline
    Registered User
    Join Date
    05-03-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Paste data from One Sheet to Another

    Quote Originally Posted by AB33 View Post
    Try this line

    Please Login or Register  to view this content.
    That worked!! Now how can I make it work using the criteria. Your first suggestion input the same result as before. The second one works but if a new word is added to that column (e.g Stop) it displays that word ,too. Also, how can I remove the formatting. because when the data gets copied to the sheet it keeps the formatting.
    Last edited by Mart1618; 05-08-2013 at 03:00 PM.
    Reply With QuoteRegister To Reply
  9. 05-08-2013, 03:12 PM #9
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Paste data from One Sheet to Another

    The link you have attached has a single criteria. The formatting issue is easy to correct, you need to paste values only, but I do not know else how the criteria can work.
    Reply With QuoteRegister To Reply
  10. 05-08-2013, 03:15 PM #10
    Mart1618
    Mart1618 is offline
    Registered User
    Join Date
    05-03-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Paste data from One Sheet to Another

    Ok, I guess I'll leave it like that. Now, how can I fix the formatting issue? I tried using PasteSpecial but I always got error messages. I tried this: 
    Please Login or Register  to view this content.
    Last edited by Mart1618; 05-08-2013 at 03:45 PM.
    Reply With QuoteRegister To Reply
  11. 05-08-2013, 05:24 PM #11
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Paste data from One Sheet to Another

    .Range("AC7:AC" & LR).Copy

    .Range("C17").PasteSpecial xlPasteValues

    Should be on two separate lines.
    I am also assuming, you are copying in to the same sheet. If this is not the case, you need to reference the second line with sheet name.
    Reply With QuoteRegister To Reply
  12. 05-09-2013, 08:31 AM #12
    Mart1618
    Mart1618 is offline
    Registered User
    Join Date
    05-03-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Paste data from One Sheet to Another

    THank you so much you are a great help!!! I accomplished what I wanted. See the code on the top. I have one last question. In this link http://stackoverflow.com/questions/6...special-in-vba
    The guy that answers the question says that we should avoid Copy/Paste. If thats true how can I make this code to work, if not thats fine:

    Please Login or Register  to view this content.
    Whenever I run that code it creates "number ones" in the sheet that I have the data. In the other sheet it only gives me the first breached ticket. Any thoughts?
    Reply With QuoteRegister To Reply
  13. 05-09-2013, 10:01 AM #13
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Paste data from One Sheet to Another

    My view and experience is quite the opposite

    Please Login or Register  to view this content.
    Does not always work, but this works all the time.

    Please Login or Register  to view this content.
    People have different experience, but you will find out for yourself when you come across one day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1