+ Reply to Thread
Results 1 to 18 of 18

Copy and Specific Paste based on Criteria

  1. #1
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Copy and Specific Paste based on Criteria

    Hello everyone and happy Friday,

    I would like to be able to run a report from a data sales which could be very long as it records all sales data from different fruits every month and every financial year.

    The thing is not every fruit has a report for the month so sometimes, there is a gap or also the report comes in later so the chronological order is not always respected on the Sales data (as highlighted in the workbook).

    Pivot can give me part of what I want but not what I really want, which is to see whether the status report of every fruit on a monthly basis (as per the Report worksheet).

    I put on the Report sheet some more explanation but really what I want to achieve is that a monthly overview of sales for a selected fruit for a selected FY(financial year) and that overview always follow the start of the FY which is May and ends in April. So if there is no report then it will be a gap on the corresponding month, and if there is a value then that will be copied on the corresponding month as well. . If there is only one report, let's say only Sep-18 for one type of fruit for the whole year, then that would be displayed on the result on the 9th row of the result and not at the top of the row as it searches for the empty one.

    What I thought:
    - Either do vba custom sort on the range E8:G19 but I think it will be tricky
    - Or add a integer j for the column but then as the FY changes, I wasn't sure how it will work.

    I'm sure there is a better way for achieving this from the existing code.

    Please Login or Register  to view this content.
    I really appreciate your help and thank you very much.

    Rako
    Attached Files Attached Files
    Last edited by rakotonirinas; 05-10-2019 at 08:09 AM. Reason: More Info

  2. #2
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    Anyone could lend a hand on this? Thank you.

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy and Specific Paste based on Criteria

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    Wow! This is incredible Sintek. Truly beyond my limited vba abilities. Thank you.

    I got lost in the code. Would you be able to explain from?

    Please Login or Register  to view this content.
    I didn't know it was possible to achieve what I asked for with a short (perhaps simple as well, if I understand all of it) code.

    Much appreciated.

    Rako

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy and Specific Paste based on Criteria

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    Sintek, thanks for the explanation. Very useful indeed and makes more sense. Thank you.

    What additional statement or code should I add if the result, instead of filling the successive 12 rows, fills every other row from the same start (E8) but ending, in effect, to the 24th row, i.e., December month will be on the cell E37?

    Much appreciated.

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy and Specific Paste based on Criteria

    Sorry I do not understand...Please clarify...Upload a sample with required result depicted...

  8. #8
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    Apologies if I wasn't very clear. Please see below image. Thank you

    As you can see from this images, there is a empty row between month result, as opposed to just a successive 12 rows of the months.

    Thank you.
    Attached Images Attached Images
    Last edited by rakotonirinas; 05-12-2019 at 12:33 PM.

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy and Specific Paste based on Criteria

    Invalid attachment...
    As per post 7
    Please clarify...Upload a sample with required result depicted...

  10. #10
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    Re-uploaded. Hope it works this time.

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy and Specific Paste based on Criteria

    Please Login or Register  to view this content.
    Last edited by sintek; 05-12-2019 at 01:24 PM.

  12. #12
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    Hi Sintek,

    Just got back on here now. Thank you again for your contribution and help.

  13. #13
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    Hi Sintek,

    Can you please elaborate on this one
    Please Login or Register  to view this content.
    -
    Checks to see if their is any available filtered data
    . I'm not sure I fully understand why the number 4 was used here for this purpose.

    Thank you.

  14. #14
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    I think I got it (I missed it first). It's the header where the data starts, I'm not sure I'm correct though.

  15. #15
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    Hi Sintek,

    I found a tiny error on the result but could not figure out how to fix it. I'm guessing it's about the incremental FY year
    Please Login or Register  to view this content.
    , and
    Please Login or Register  to view this content.
    .

    As you can see from the result, the Months of the FY19 is all on the same year, whereas it should be as per the data, May-18 to Dec-18 (2018) and Jan-19 to Apr-19 (2019).

    Is there any way to fix this?

    Thank you.
    Attached Files Attached Files

  16. #16
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy and Specific Paste based on Criteria

    Change declaration of...
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    09-02-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    368

    Re: Copy and Specific Paste based on Criteria

    That did it Sintek. Thank you.
    It never crossed my mind to check the variable to solve it. I was thinking probably using an If statement to make the distinction between FY.
    Incredible and very well done!

  18. #18
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy and Specific Paste based on Criteria

    Pleasure....

+ 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. VBA Macro: Copy and Paste Specific Row Range based on the Specific Value in a Cell.
    By SolidSmoke in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-10-2016, 02:40 PM
  2. [SOLVED] Copy and paste specific cell to another sheet based on multiple criteria
    By impresxy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-09-2015, 07:11 AM
  3. Copy Paste based on Specific Criteria
    By kittu55 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-06-2015, 08:58 AM
  4. [SOLVED] VBA - Copy and Paste data based on two criteria to specific start row and column
    By ndgo10 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-02-2014, 09:15 AM
  5. [SOLVED] Copy entire row based on 2 criteria and paste on the next blank row on a specific sheet
    By ALNER in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2014, 11:23 PM
  6. Replies: 0
    Last Post: 07-16-2013, 05:22 AM
  7. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM

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