Stop Copying at Specific Row

[smandula]

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

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

 

[Bob Phillips]

Sub CopyRange2()
Dim iLastRow As Long

With Sheets("Sheet1")
iLastRow = .Range("B8").End(xlDown).Row
If iLastRow > 18 Then
iLastRow = 18
End If
.Range("B5:G5").Copy
.Range("B" & iLastRow + 1).PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = True

Range("A1").Select

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

try this if you always want rows 8-18. If not, add to this post.

Sub copy5to18()
Range("b8:g18").Value = Range("b5:g5").Value
End Sub

 

[smandula]

Thanks for your examples.

But, alas they don't work.
One copies to the entire range
the other keeps going past row B18

Thanks for your prompt response
Much appreciated.

 

[Dave Peterson]

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]

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

 

[smandula]

Mystery solved.

Formatting works just fine.
VBA works even better.

Thanks a Million.
Thanks for your Help.

Bye for Now
Steve

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]

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.

Mystery solved.

Formatting works just fine.
VBA works even better.

Thanks a Million.
Thanks for your Help.

Bye for Now
Steve