+ Reply to Thread
Results 1 to 8 of 8

VBA code for looping and copy/pasting data from other sheet

  1. #1
    Registered User
    Join Date
    05-22-2020
    Location
    Midlands, England
    MS-Off Ver
    2016
    Posts
    8

    VBA code for looping and copy/pasting data from other sheet

    Hi all. I would like to know if someone could help me out finding with VBA the way of completing a task that takes ages to do manually. I heard that Excel could do the same process with VBA coding.

    What I want to do is fill in the tables in “Planner” sheet simultaneously by copying and pasting the values from “Breaks” sheet.

    The criteria are, where Start and End time match both in Planner and Breaks sheets, value of Break1, Break2 & Break3 needs to be copied and pasted in Planner sheet tables for each day.

    The number of Names as well as the Start and End variables differ from Team1 to Team2 and from Day1 to Day2.

    I have added a third sheet showing the expected outcome of the macro.

    I would appreciate if any help can be offered on this matter.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,733

    Re: VBA code for looping and copy/pasting data from other sheet

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    F
    G
    4
    Team1
    5
    Name
    Start
    End
    Break1
    Break2
    Break3
    6
    Name1
    07:00
    15:30
    =VLOOKUP(C6,Breaks!$A$2:$E$28,3,0)
    =VLOOKUP(C6,Breaks!$A$2:$E$28,4,0)
    =VLOOKUP(C6,Breaks!$A$2:$E$28,5,0)
    7
    Name2
    07:00
    15:30
    =IF(C7=C6,E6+0.01389,VLOOKUP(C7,Breaks!$A$2:$E$28,3,0))
    =IF(C7=C6,F6+0.0105,VLOOKUP(C7,Breaks!$A$2:$E$28,4,0))
    =IF(C7=C6,G6+0.00695,VLOOKUP(C7,Breaks!$A$2:$E$28,5,0))
    8
    Name3
    07:00
    15:30
    =IF(C8=C7,E7+0.01389,VLOOKUP(C8,Breaks!$A$2:$E$28,3,0))
    =IF(C8=C7,F7+0.0105,VLOOKUP(C8,Breaks!$A$2:$E$28,4,0))
    =IF(C8=C7,G7+0.00695,VLOOKUP(C8,Breaks!$A$2:$E$28,5,0))
    9
    Name4
    09:00
    17:30
    =IF(C9=C8,E8+0.01389,VLOOKUP(C9,Breaks!$A$2:$E$28,3,0))
    =IF(C9=C8,F8+0.0105,VLOOKUP(C9,Breaks!$A$2:$E$28,4,0))
    =IF(C9=C8,G8+0.00695,VLOOKUP(C9,Breaks!$A$2:$E$28,5,0))
    10
    Name5
    09:00
    17:30
    =IF(C10=C9,E9+0.01389,VLOOKUP(C10,Breaks!$A$2:$E$28,3,0))
    =IF(C10=C9,F9+0.0105,VLOOKUP(C10,Breaks!$A$2:$E$28,4,0))
    =IF(C10=C9,G9+0.00695,VLOOKUP(C10,Breaks!$A$2:$E$28,5,0))
    11
    Name6
    09:00
    17:30
    =IF(C11=C10,E10+0.01389,VLOOKUP(C11,Breaks!$A$2:$E$28,3,0))
    =IF(C11=C10,F10+0.0105,VLOOKUP(C11,Breaks!$A$2:$E$28,4,0))
    =IF(C11=C10,G10+0.00695,VLOOKUP(C11,Breaks!$A$2:$E$28,5,0))
    12
    Name7
    09:00
    17:30
    =IF(C12=C11,E11+0.01389,VLOOKUP(C12,Breaks!$A$2:$E$28,3,0))
    =IF(C12=C11,F11+0.0105,VLOOKUP(C12,Breaks!$A$2:$E$28,4,0))
    =IF(C12=C11,G11+0.00695,VLOOKUP(C12,Breaks!$A$2:$E$28,5,0))
    13
    Name8
    11:30
    20:00
    =IF(C13=C12,E12+0.01389,VLOOKUP(C13,Breaks!$A$2:$E$28,3,0))
    =IF(C13=C12,F12+0.0105,VLOOKUP(C13,Breaks!$A$2:$E$28,4,0))
    =IF(C13=C12,G12+0.00695,VLOOKUP(C13,Breaks!$A$2:$E$28,5,0))
    14
    Name9
    11:30
    20:00
    =IF(C14=C13,E13+0.01389,VLOOKUP(C14,Breaks!$A$2:$E$28,3,0))
    =IF(C14=C13,F13+0.0105,VLOOKUP(C14,Breaks!$A$2:$E$28,4,0))
    =IF(C14=C13,G13+0.00695,VLOOKUP(C14,Breaks!$A$2:$E$28,5,0))
    15
    Name10
    13:30
    22:00
    =IF(C15=C14,E14+0.01389,VLOOKUP(C15,Breaks!$A$2:$E$28,3,0))
    =IF(C15=C14,F14+0.0105,VLOOKUP(C15,Breaks!$A$2:$E$28,4,0))
    =IF(C15=C14,G14+0.00695,VLOOKUP(C15,Breaks!$A$2:$E$28,5,0))
    16
    Name11
    13:30
    22:00
    =IF(C16=C15,E15+0.01389,VLOOKUP(C16,Breaks!$A$2:$E$28,3,0))
    =IF(C16=C15,F15+0.0105,VLOOKUP(C16,Breaks!$A$2:$E$28,4,0))
    =IF(C16=C15,G15+0.00695,VLOOKUP(C16,Breaks!$A$2:$E$28,5,0))
    17
    Name12
    13:30
    22:00
    =IF(C17=C16,E16+0.01389,VLOOKUP(C17,Breaks!$A$2:$E$28,3,0))
    =IF(C17=C16,F16+0.0105,VLOOKUP(C17,Breaks!$A$2:$E$28,4,0))
    =IF(C17=C16,G16+0.00695,VLOOKUP(C17,Breaks!$A$2:$E$28,5,0))
    18
    Name13
    13:30
    22:00
    =IF(C18=C17,E17+0.01389,VLOOKUP(C18,Breaks!$A$2:$E$28,3,0))
    =IF(C18=C17,F17+0.0105,VLOOKUP(C18,Breaks!$A$2:$E$28,4,0))
    =IF(C18=C17,G17+0.00695,VLOOKUP(C18,Breaks!$A$2:$E$28,5,0))
    19
    Name14
    13:30
    22:00
    =IF(C19=C18,E18+0.01389,VLOOKUP(C19,Breaks!$A$2:$E$28,3,0))
    =IF(C19=C18,F18+0.0105,VLOOKUP(C19,Breaks!$A$2:$E$28,4,0))
    =IF(C19=C18,G18+0.00695,VLOOKUP(C19,Breaks!$A$2:$E$28,5,0))
    20
    Name15
    15:30
    00:00
    =IF(C20=C19,E19+0.01389,VLOOKUP(C20,Breaks!$A$2:$E$28,3,0))
    =IF(C20=C19,F19+0.0105,VLOOKUP(C20,Breaks!$A$2:$E$28,4,0))
    =IF(C20=C19,G19+0.00695,VLOOKUP(C20,Breaks!$A$2:$E$28,5,0))
    21
    Name16
    15:30
    00:00
    =IF(C21=C20,E20+0.01389,VLOOKUP(C21,Breaks!$A$2:$E$28,3,0))
    =IF(C21=C20,F20+0.0105,VLOOKUP(C21,Breaks!$A$2:$E$28,4,0))
    =IF(C21=C20,G20+0.00695,VLOOKUP(C21,Breaks!$A$2:$E$28,5,0))
    Sheet: Planner
    Last edited by alansidman; 05-23-2020 at 03:54 PM.

  3. #3
    Registered User
    Join Date
    05-22-2020
    Location
    Midlands, England
    MS-Off Ver
    2016
    Posts
    8

    Re: VBA code for looping and copy/pasting data from other sheet

    Thank you alansidman for your reply. In this case the formulas did not work in my sheet. Nevertheless, the process explained is to be replicated hundreds of times and the amount of input data to look for has thousands of rows. This is basically the reason I am looking to automate the process with a VBA code😊

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,544

    Re: VBA code for looping and copy/pasting data from other sheet

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    05-22-2020
    Location
    Midlands, England
    MS-Off Ver
    2016
    Posts
    8

    Re: VBA code for looping and copy/pasting data from other sheet

    Apologise FDibbins for not complying with Forum rule 3. I am new to this Forum site and did not go through the rules before. Thanks for bringing this to my attention. I will try to adhere to the Forum rules moving forward.

    The question was also posted in MrExcel. I was not aware how important was not to disturb other with the same query as this may waste their time as well.

    I truly would like to be part of the community and I will stick to rules moving forward.

    I do understand if you don't help me out with the query anymore. Nevertheless, I wanted to post the code used for a single team in a single day. However, I can't until I have posted a few times. Now, I believe I need to re-do the code using Ranges as this needs to be done multiple times in a day and on various days. Therefore, a loop is also required to replicate the process through all the ranges simultaneously. However, I do struggle to figure out how to define the variables to make it logic code for VBA. I would appreciate if you could provide some guidelines on how to define my variables and conduct a loop that could do the task multiple times.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,733

    Re: VBA code for looping and copy/pasting data from other sheet

    Since you have posted on MrE. It is important that you indicate the thread link as noted in the rules.

    Interesting that you say that the formula I provided did not work. See the attached. I only provide one example as it is easy to replicate. If your sample is not representative of your actual file then please amend your file to make it representative. My result looks exactly like your expected results.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-22-2020
    Location
    Midlands, England
    MS-Off Ver
    2016
    Posts
    8

    Re: VBA code for looping and copy/pasting data from other sheet

    I have tested it again and the formulas you added in your initial reply work perfectly. I did not realize that recently I updated the Excel version I use at home (Excel version 2019) and after replacing the commas with ; it has worked. I should have mention in my reply that instead of testing it with the Excel version 2016 I use in the office, I have used the new version. I tested now with version 2019.

    I can't past the thread link as I need to post few times before being able to do so. However, this is the way I can provide the thread: /vba-to-loop-on-ranges.1135006

    Both options worked perfectly well. I hope I can help other same as you did with me moving forward.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,733

    Re: VBA code for looping and copy/pasting data from other sheet

    Regional settings differ in Excel. In the US, we use commas to separate parts of a formula. Many European locations use semi-colons as you have determined.

+ 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. Copy Data from current sheet with Auto Filter/Looping then paste to another sheet
    By stepkwan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2020, 09:04 AM
  2. [SOLVED] VBA Code: Pasting Data to Another Sheet
    By Jim Clayton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2018, 04:14 PM
  3. Matching data from one sheet to another and pasting data (looping problem)
    By caseylooper in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2017, 01:22 PM
  4. Replies: 5
    Last Post: 02-19-2016, 06:17 PM
  5. Looping instead of copy and pasting
    By tangerinedream in forum Excel General
    Replies: 3
    Last Post: 12-26-2014, 08:20 PM
  6. Copy and Pasting, offsetting and looping
    By Milbourn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2014, 09:45 AM
  7. Looping Code for Pasting
    By smlaff01 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-12-2008, 02:13 PM

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