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

**casper_01** · 05-23-2020, 03:08 PM

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.

**alansidman** · 05-23-2020, 03:49 PM

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

**casper_01** · 05-23-2020, 03:58 PM

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😊

**FDibbins** · 05-23-2020, 04:31 PM

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.)

**casper_01** · 05-23-2020, 04:57 PM

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.

**alansidman** · 05-23-2020, 05:54 PM

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.

**casper_01** · 05-23-2020, 06:07 PM

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.

**alansidman** · 05-23-2020, 11:49 PM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Copy Data from current sheet with Auto Filter/Looping then paste to another sheet

[SOLVED] VBA Code: Pasting Data to Another Sheet

Matching data from one sheet to another and pasting data (looping problem)

How to reproduce filtered data on a table from one sheet to other sheet w/o copy-pasting?

Looping instead of copy and pasting

Copy and Pasting, offsetting and looping

Looping Code for Pasting

Bookmarks

Bookmarks

Posting Permissions