ExcelBanter - Stop Copying at Specific Row

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Stop Copying at Specific Row (https://www.excelbanter.com/excel-programming/326242-stop-copying-specific-row.html)

smandula

Stop Copying at Specific Row

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[_6_]

Stop Copying at Specific Row

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)

"smandula" wrote in message
...
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

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

Don Guillett[_4_]

Stop Copying at Specific Row

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

--
Don Guillett
SalesAid Software

"smandula" wrote in message
...
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

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

smandula

Stop Copying at Specific Row

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.

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[_5_]

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

smandula

Stop Copying at Specific Row

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

smandula

Stop Copying at Specific Row

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[_5_]

Stop Copying at Specific Row

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

All times are GMT +1. The time now is 02:18 AM.