Results 1 to 7 of 7

Thread: VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook

    Hi, Hope you are all well. You will see below two images which represent 2 separate workbooks. The first is the Source workbook and the second is the Destination workbook. What I need help in doing is producing VBA code that will copy the relevant information from the Source workbook into the Destination workbook as it's laid out in the second image.


    If we assume the Destination workbook is blank to begin with, some of the information is fixed and won't need copying across from the Source workbook but will need to be factored in by the VBA code. For example most of the column headings apart from the League Name, which is variable and the Home & Away text in column B will always be the same.


    The League Name can be found in cell E2 of the Source file.


    The date can be found in the relevant row in column F of the Source file.


    The main part of the code needs to look at each fixture in columns F to H of the Source file coming up in the next 7 days then match the Home team in column G to the Home Rankings table in column P and copy that row of data from P to X into the Destination workbook(in columns C to K). Then match the Away team in column H to the Away Rankings table in column Z and copy that row of data from Z to AH into the next row down of the Destination workbook(in columns C to K).


    I will be running the code on more than one source file but i won't need it to loop through each one. I will run the code on the first source file then close it and open the second source file then run the code again. So it will need to look for the first blank row in the Destination file each time it runs. The structure of each source file is exactly the same so as long as it works for the first it will run for them all.


    I have added links below to sample files on Dropbox for you to work with.

    Source File - Dropbox - sample_data.xlsx

    Destination File - Dropbox - Destination.xlsx

    I'm really hoping some kind soul can help me please. I've been trying to do this myself for the last couple of weeks but being a novice have had to admit defeat. I hope i've explained things well enough but please ask if you need anything clarifying.

    Source Workbook
    Excel 2013/2016
    A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH
    1
    2 League Name HOME RANKINGS AWAY RANKINGS
    3
    4
    5
    6 Rd Date Home Away Team F H FHH SHH ATH DFH OFH RFH Team F A FHA SHA ATA DFA OFA RFA
    7 1 21/08/17 Team 1 Team 30 Team 1 2 3 9 3 4 18 3 3 Team 1 2 10 4 21 9 17 10 10
    8 1 21/08/17 Team 2 Team 29 Team 2 3 4 4 7 2 13 2 3 Team 2 3 11 26 12 9 21 3 4
    9 1 21/08/17 Team 3 Team 28 Team 3 8 5 2 10 8 1 8 8 Team 3 8 17 10 24 17 5 8 5
    10 1 21/08/17 Team 4 Team 27 Team 4 30 29 7 30 27 25 27 30 Team 4 30 27 22 22 22 9 30 29
    11 1 21/08/17 Team 5 Team 26 Team 5 13 27 27 22 24 22 24 13 Team 5 13 1 5 3 3 1 13 27
    12 1 21/08/17 Team 6 Team 25 Team 6 7 8 6 14 5 17 5 7 Team 6 7 14 14 17 15 21 7 8
    13 1 21/08/17 Team 7 Team 24 Team 7 18 11 17 8 10 19 10 18 Team 7 18 26 23 23 27 9 18 11
    14 1 21/08/17 Team 8 Team 23 Team 8 10 2 3 2 3 13 3 10 Team 8 10 24 7 30 24 5 10 2
    15 1 21/08/17 Team 9 Team 22 Team 9 6 10 15 5 5 25 5 6 Team 9 6 8 12 9 7 13 6 10
    16 1 21/08/17 Team 10 Team 21 Team 10 28 18 10 23 15 27 15 28 Team 10 28 30 30 28 29 27 28 18
    17 1 21/08/17 Team 11 Team 20 Team 11 12 7 23 1 13 3 13 12 Team 11 12 21 18 16 22 27 12 7
    18 1 21/08/17 Team 12 Team 19 Team 12 4 1 1 6 1 13 1 4 Team 12 4 25 17 26 24 5 4 1
    19 1 21/08/17 Team 13 Team 18 Team 13 25 25 19 27 24 30 24 25 Team 13 25 19 29 6 17 21 25 25
    20 1 21/08/17 Team 14 Team 17 Team 14 16 13 16 11 8 19 8 16 Team 14 16 15 6 29 9 9 16 13
    21 1 21/08/17 Team 15 Team 16 Team 15 22 17 13 19 22 1 22 22 Team 15 22 18 25 15 17 17 22 17
    22 Team 16 27 28 18 29 24 27 24 27 Team 16 27 23 19 18 27 3 27 28
    23 2 04/09/17 Team 30 Team 16 Team 17 5 9 8 13 10 8 10 5 Team 17 5 7 8 20 5 17 5 9
    24 2 04/09/17 Team 17 Team 15 Team 18 9 22 29 12 22 8 22 9 Team 18 9 3 3 1 1 2 9 22
    25 2 04/09/17 Team 18 Team 14 Team 19 14 12 14 9 10 8 10 14 Team 19 14 13 20 8 17 13 14 12
    26 2 04/09/17 Team 19 Team 13 Team 20 11 16 20 16 17 13 17 11 Team 20 11 5 9 5 7 9 11 16
    27 2 04/09/17 Team 20 Team 12 Team 21 19 26 22 25 27 5 27 19 Team 21 19 4 1 4 4 8 19 26
    28 2 04/09/17 Team 21 Team 11 Team 22 23 24 12 26 19 22 19 23 Team 22 23 12 15 13 15 3 23 24
    29 2 04/09/17 Team 22 Team 10 Team 23 1 14 5 24 13 8 13 1 Team 23 1 2 2 10 1 21 1 14
    30 2 04/09/17 Team 23 Team 9 Team 24 24 21 25 20 27 4 27 24 Team 24 24 16 13 14 9 27 24 21
    31 2 04/09/17 Team 24 Team 8 Team 25 21 15 21 15 17 8 17 21 Team 25 21 22 16 11 9 30 21 15
    32 2 04/09/17 Team 25 Team 7 Team 26 17 6 11 4 7 5 7 17 Team 26 17 28 24 25 24 21 17 6
    33 2 04/09/17 Team 26 Team 6 Team 27 15 20 26 18 19 5 19 15 Team 27 15 6 21 2 5 17 15 20
    34 2 04/09/17 Team 27 Team 5 Team 28 20 23 28 21 19 22 19 20 Team 28 20 9 11 19 9 21 20 23
    35 2 04/09/17 Team 28 Team 4 Team 29 26 19 30 17 15 27 15 26 Team 29 26 29 28 27 29 13 26 19
    36 2 04/09/17 Team 29 Team 3 Team 30 29 30 24 28 30 19 30 29 Team 30 29 20 27 7 17 13 29 30
    37 2 04/09/17 Team 1 Team 2
    38
    39 3 05/09/17 Team 2 Team 30
    40 3 05/09/17 Team 3 Team 1
    41 3 05/09/17 Team 4 Team 29
    42 3 05/09/17 Team 5 Team 28
    43 3 05/09/17 Team 6 Team 27
    44 3 05/09/17 Team 7 Team 26
    45 3 05/09/17 Team 8 Team 25
    46 3 05/09/17 Team 9 Team 24
    47 3 05/09/17 Team 10 Team 23
    48 3 05/09/17 Team 11 Team 22
    49 3 05/09/17 Team 12 Team 21
    50 3 05/09/17 Team 13 Team 20
    Sheet1



    Destination Workbook
    Excel 2013/2016
    A B C D E F G H I J K
    1 Date League Name F H FH SH At DF OF RF
    2 04/09/17 Home Team 30 13 15 4 16 11 15 16 15
    3 04/09/17 Away Team 16 7 14 13 13 11 13 2 2
    4 04/09/17 Home Team 17 7 1 1 3 1 8 7 5
    5 04/09/17 Away Team 15 11 7 7 4 10 13 5 3
    6 04/09/17 Home Team 18 9 13 3 8 1 7 11 5
    7 04/09/17 Away Team 14 12 9 8 3 1 15 6 7
    8 04/09/17 Home Team 19 4 6 5 8 6 4 4 5
    9 04/09/17 Away Team 13 9 18 18 12 12 17 7 7
    10 04/09/17 Home Team 20 4 8 11 5 5 14 3 9
    11 04/09/17 Away Team 12 10 8 1 13 4 10 15 6
    12 04/09/17 Home Team 21 7 1 1 3 1 8 7 5
    13 04/09/17 Away Team 11 11 7 7 4 10 13 5 3
    14 04/09/17 Home Team 22 9 13 3 8 1 7 11 5
    15 04/09/17 Away Team 10 12 9 8 3 1 15 6 7
    16 04/09/17 Home Team 23 4 6 5 8 6 4 4 5
    17 04/09/17 Away Team 9 9 18 18 12 12 17 7 7
    18 04/09/17 Home Team 24 4 8 11 5 5 14 3 9
    19 04/09/17 Away Team 8 10 8 1 13 4 10 15 6
    20 04/09/17 Home Team 25 7 1 1 3 1 8 7 5
    21 04/09/17 Away Team 7 11 7 7 4 10 13 5 3
    22 04/09/17 Home Team 26 9 13 3 8 1 7 11 5
    23 04/09/17 Away Team 6 12 9 8 3 1 15 6 7
    24 04/09/17 Home Team 27 4 6 5 8 6 4 4 5
    25 04/09/17 Away Team 5 9 18 18 12 12 17 7 7
    26 04/09/17 Home Team 28 4 8 11 5 5 14 3 9
    27 04/09/17 Away Team 4 10 8 1 13 4 10 15 6
    28 04/09/17 Home Team 29 10 8 1 13 4 10 15 6
    29 04/09/17 Away Team 3 10 8 1 13 4 10 15 6
    30 04/09/17 Home Team 1 10 8 1 13 4 10 15 6
    31 04/09/17 Away Team 2 10 8 1 13 4 10 15 6
    32 Date League 2 Name F H FH SH At DF OF RF
    33 04/09/17 Home Team 4 7 1 1 3 1 8 7 5
    34 04/09/17 Away Team 2 11 7 7 4 10 13 5 3
    35 04/09/17 Home Team 5 9 13 3 8 1 7 11 5
    36 04/09/17 Away Team 6 12 9 8 3 1 15 6 7
    37 04/09/17 Home Team 7 4 6 5 8 6 4 4 5
    38 04/09/17 Away Team 8 9 18 18 12 12 17 7 7
    39 04/09/17 Home Team 9 4 8 11 5 5 14 3 9
    40 04/09/17 Away Team 10 4 6 5 8 6 4 4 5
    Sheet1

  2. #2
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook

    Just wanted to add that the example source file i've provided with 30 teams is the maximum number of teams that any other source file will have so the code doesn't need to do full column ranges. Columns F to H will only need to go up to row 933 and columns P to X and Z to AH will only need to go to row 36. All other source files will fit within those ranges. Thanks.

  3. #3
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook

    One final thing which might be worth mentioning is that the Source filenames will be different so it would be great if a specific filename wasn't referenced in the code for that but the Destination filename will always be the same so that can be. Thanks.

  4. #4
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook

    Posted this in the wee hours. Hoping someone can help me with the above if possible please.
    Last edited by MrExcel; Aug 30th, 2017 at 08:23 AM.

  5. #5
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    873
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook

    Would you consider this:
    1) Have the macro code sitting inside the destination workbook
    2) To run the macro, you would open the destination workbook and keep it open in the background. You would then open the Source workbook. Press a hotkey to invoke the macro.
    3) The macro would always pull data from the active workbook and place the records in the destination workbook below any previous records.
    View a collection of recent Excel articles in the Excel Daily News

  6. #6
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook

    Hi MrExcel. Yes option 2 would be my preference please. Thanks.

  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook

    Just wanted to mention that the date format in column F of the Source file has now changed from my original post and sample data. It now includes the time and is formatted as follows 04.09. 19:30

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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