Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,


    I have a problem regarding copy and paste data in EXCEL

    I need a macro that will help me copying and pasting data when I am using Filter.


    Suppose, I have a workbook( BOOk 1) that some data in cells A2:C10. and I used filter to hide rows 4,5,and 6. I have also used ALT+; key to select only visible cells.



    I have another Workbook( Book 2). I used filter in this workbook as well and now I want to paste the visible cells data of Book 1 into Book 2 of selected VISIBLE RANGE.


    In simple, I am in need of a macro that will simply copy the data from selected range of visible cells and paste into another selected range of visible cells.


    Any help will be appreciated.

    Thanks in Advance.


    ANkit

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    It does not make sense to paste the data into a visible cells range?!

    I am assuming that Book 2 is filtered with data that is similar to the copied data in Book 1. So if you want to add the data to Book 2, simply paste the data into Book 2, at the bottom of the list, and after you have pasted it, then apply the filter. This will produce the result you need. (Assuming I have deduced your scenario correctly).
    Regards,
    Rudi

  3. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    [quote name='Rudi' post='762201' date='27-Feb-09 15:48']It does not make sense to paste the data into a visible cells range?!

    I am assuming that Book 2 is filtered with data that is similar to the copied data in Book 1. So if you want to add the data to Book 2, simply paste the data into Book 2, at the bottom of the list, and after you have pasted it, then apply the filter. This will produce the result you need. (Assuming I have deduced your scenario correctly).[/quote]





    No, actually I want to copy a filetered column from Book 1 and paste it into a filtered column of Book 2. Let me explain it a bit more.

    Book 2 contains: In first column...Dates suppose 2/1/09 till 2/28/09 row wise...In front of each date there is a name Suppose "Ankit" and this is for a record of 5 Employees.


    2/1/09 Ankit
    2/2/09 Ankit
    2/3/09 Ankit
    .
    .
    .
    .
    .
    .2/28/09 Ankit
    2/1/09 Sam
    2/2/09 Sam
    2/3/09 Sam
    .
    .
    .
    .
    2/28/09 Sam






    Now I have in third column for some data and so on.

    I have book 1 that contains data column wise for every date.

    I use to enter everyday date wise in Book2.

    Now when I apply filter in Book 2 and select a date suppose 2/1/09, and wants to enter data for every person for date 2/1/09. It doesn't allow me to do so ..because pasting values in filtered cells is not permissible.


    For this purpose, I want a macro.



    I think I have explained it clearly now.



    Please help.


    thanks,
    Ankit

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Ankit,

    I understand what book 2 looks like now, and also how you are filtering the data, but I am still unclear about the structure of Book 1, where you are wanting to paste the data. If you could provide an example of what book 1 looks like (you say dates are in columns?), then it may be clearer to understand what methods to use to solve your query.

    Sorry for my failed attempts to comprehend your attempts to explain.

    By the way...
    If you are wanting a macro for this process, I would recommend that you zip a sample of Book 1 and Book2 and then attach it into a reply. In order to create macros, it is best to see what the structure of the data looks like.
    Regards,
    Rudi

  5. #5
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    [quote name='Rudi' post='762388' date='28-Feb-09 09:37']Hi Ankit,

    I understand what book 2 looks like now, and also how you are filtering the data, but I am still unclear about the structure of Book 1, where you are wanting to paste the data. If you could provide an example of what book 1 looks like (you say dates are in columns?), then it may be clearer to understand what methods to use to solve your query.

    Sorry for my failed attempts to comprehend your attempts to explain.

    By the way...
    If you are wanting a macro for this process, I would recommend that you zip a sample of Book 1 and Book2 and then attach it into a reply. In order to create macros, it is best to see what the structure of the data looks like.[/quote]



    Hi Rudi,

    Please see the attached Workbooks. I have attached Book1 and Book2 as well.....Book 1 contains the data as Downtime, Productivity and Attempts in front of Each name for only date 2/1/09.

    I want to paste the data of Book1 data for date 2/1/09 into Book2 in the same columns in Book for only date 2/1/09...that I have already filtered.


    Thanks in advance for your concern.


    Regards,
    Ankit
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This is typically the kind of thing you should do in Microsoft Access, not in Excel. In Access it's a "natural" problem: you can use an update query that will update only the records satisfying certain conditions (corresponding to your filter). In Excel it's very artificial.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    I am not sure if this is the best way of doing it, but I did get the result you are after simply by using a VLOOKUP function. (See the attached picture of the solution).

    In Book1, I inserted a new column A, and used a concatenated formula (=B2&C2), to join the date and name. The Book1 data becomes the Table Array argument in the VLOOKUP.

    In Book2, I did the same concatenate formula in column A.
    Then in the three columns Downtime, Productivity and Attempts, you create the formula: =VLOOKUP($A2,[Book1.xlsx]Sheet1!$A$2:$F$7,4,FALSE)

    This collects the data from Book1 and places the info in the correct row and columns.

    [attachment=82498:Capture.JPG]
    Attached Images Attached Images
    Regards,
    Rudi

Posting Permissions

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