# Solved Spreadsheet data Copy and Paste with loop

January 26, 2013 at 03:42:10
Specs: pc
 Hi,I have a spreadsheet with patient id and patient name on the first row and service details on the following rows such as dates and provider names.patient id1 patient name1serv date1 providerserv date2 provider Total for client: 2Patient id2 patient name2serv date1 providerserv date2 providerserv date3 provider Total for client: 3Patient id3 patient name3serv date1 provider Total for client: 1Patient id4 patient name4serv date1 providerserv date2 providerserv date3 provider Total for client: 3My question is how do I write a macro to copy each patient id and patient name and paste them right next to each of their serv dates with a loop. Thank you so much in advance. Your help is greatly appreciated.Thanks,Tiffany

See More: Spreadsheet data Copy and Paste with loop

#1
January 26, 2013 at 09:47:43
 Hi TiffanyHere is a rough start that makes a bunch of assumptions likeyour data is in columns 1 and 2the first patient ID and Name is in Row 1The word "Total" is the key to when a new patient name will start on the next rowthere are no blank rowsLet me know if any of these assumptions are false and what the real case is so that we can adjust the macro.Otherwise, give this a try on a Copy of your spreadsheet. ```Sub PatientIDs() LastRow = Range("A" & Rows.Count).End(xlUp).Row 'Get first set starting on 1st Row assuming this is where the first name is pID = Cells(1, 1) pName = Cells(1, 2) For i = 2 To LastRow 'Look for the word "total" in the previous row to identify the name and ID If UCase(Left(Cells(i - 1, 1), 5)) = "TOTAL" Then pID = Cells(i, 1) pName = Cells(i, 2) 'Otherwise place the ID and name in Cols 3 and 4 Else Cells(i, 3) = pID Cells(i, 4) = pName End If Next End Sub```This is how I imagine your data looks like``` Col A Col B Row 1 patient id2 patient name2 Row 2 serv date1 provider Row 3 serv date2 provider Row 4 Total for Client 2 Row 5 patient id3 patient name3 Row 6 serv date1 provider Row 7 serv date2 provider Row 8 serv date3 provider Row 9 Total for Client 3 Row 10 patient id4 patient name4 Row 11 serv date1 provider Row 12 Total for Client 1 Row 13 patient id5 patient name5 Row 14 serv date1 provider Row 15 serv date2 provider Row 16 serv date3 provider Row 17 Total for Client 3 ```and this would be the result``` Col A Col B Col C Col D Row 1 patient id2 patient name2 Row 2 serv date1 provider patient id2 patient name2 Row 3 serv date2 provider patient id2 patient name2 Row 4 Total for Client 2 patient id2 patient name2 Row 5 patient id3 patient name3 Row 6 serv date1 provider patient id3 patient name3 Row 7 serv date2 provider patient id3 patient name3 Row 8 serv date3 provider patient id3 patient name3 Row 9 Total for Client 3 patient id3 patient name3 Row 10 patient id4 patient name4 Row 11 serv date1 provider patient id4 patient name4 Row 12 Total for Client 1 patient id4 patient name4 Row 13 patient id5 patient name5 Row 14 serv date1 provider patient id5 patient name5 Row 15 serv date2 provider patient id5 patient name5 Row 16 serv date3 provider patient id5 patient name5 Row 17 Total for Client 3 patient id5 patient name5 ```