Copy Paste Code

[Herbiec09]

Hi All,

I am looking for assistance with a unique copy and paste problem. Can someone assist with a code to copy data into the areas that I have highlighted in green from the rows above. I have put an attachment of the spreadsheet.


Thank you

Herbert

[patel]

Sub a()
r1 = 4
r = 5
Do
If Left(Cells(r, 1), 4) = "Shop" Or Left(Cells(r, 1), 4) = "Test" Then
Range("A" & r1 & ":F" & r1).Copy Range("A" & r1 + 1 & ":F" & r - 1)
If Left(Cells(r, 1), 4) = "Test" Then Exit Do
r1 = r
r = r + 1
Else
r = r + 1
End If
Loop
End Sub

[Herbiec09]

Many thanks for the code, could kindly walk me through what it is actually doing at each step.

Thanks

Herbert

[Herbiec09]

Many thanks for the code, could kindly walk me through what it is actually doing at each step.

Thanks

Herbert

[patel]

Sub a()
r1 = 4 'initial row with Shop
r = 5 ' initial row for scanning
Do
If Left(Cells(r, 1), 4) = "Shop" Or Left(Cells(r, 1), 4) = "Test" Then
Range("A" & r1 & ":F" & r1).Copy Range("A" & r1 + 1 & ":F" & r - 1) ' copy the first line into the range between two Shop
If Left(Cells(r, 1), 4) = "Test" Then Exit Do ' if Test fount the loop stops
r1 = r ' the last shop becomes the first
r = r + 1 ' increment row
Else
r = r + 1 ' no shop found
End If
Loop
End Sub the code is very simple, you can run it step by step with F8