Actually, the formatting doesn't stay as it was. It gets copied from B5:G5. If
you have a different format, then copy|paste special|values (or just assignment
of values) may be better.

smandula wrote:

Mystery solved.

Formatting works just fine.
VBA works even better.

Thanks a Million.
Thanks for your Help.

Bye for Now
Steve

"smandula" wrote in message
...
This is excellent!
Works great.

However, only a minor point, in that G8:G18 any formatting, i.e. box
border is erased.
Any idea why each box bordering is erased.

That's for the elegant solution.

With Thanks
Steve




"Dave Peterson" wrote in message
...
How about this:

Option Explicit
Sub CopyRange2a()

Dim DestCell As Range

With Worksheets("sheet1")
Set DestCell = .Range("b8")
Do
If DestCell.Row 18 Then
Set DestCell = Nothing
Exit Do
ElseIf Application.CountA(DestCell.Resize(1, 6)) = 0 Then
Exit Do
Else
Set DestCell = DestCell.Offset(1, 0)
End If
Loop
If DestCell Is Nothing Then
Beep
'or
'MsgBox "No room left!"
Else
.Range("b5").resize(1,6).Copy _
Destination:=DestCell
Application.CutCopyMode = False
End If

Application.Goto .Range("a1"), scroll:=True

End With

End Sub

It's just looking brute force for the first row with columns B:G empty
(between
rows 8 and 18).




smandula wrote:

How do I stop copying, when the copy reaches B18:G18?
Here is the VBA that I use.

It copy's data from row B5:G5 to beginning line B8.
everytime the command copy button is pushed.
I want the copying to stop at row B18
then park the cursor everytime at A1

With Thanks
Steve

---------------------------------------------------------------------------------------

Sub CopyRange2()
Sheets("Sheet1").Range("B8").Select
'find your empty cell
Do Until ActiveCell.Formula = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Formula = Range("B5:G5").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = True

'Stop copying when the copying reaches B18:G18

Range("A1").Select

End Sub

------------------------------------------------------------------

--

Dave Peterson



--

Dave Peterson