Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ------------------------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ------------------------------------------------------------------ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ------------------------------------------------------------------ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Stop calculation on one specific sheets only | Excel Worksheet Functions | |||
Stop the 'intelligent' copying? | Excel Discussion (Misc queries) | |||
Stop the 'intelligent' copying? | Excel Discussion (Misc queries) | |||
Stop specific cell from recalculating? | Excel Worksheet Functions | |||
A way to stop or limit copying ? | Excel Programming |