Stop Copying at Specific Row
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
|