Help with Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RoadKing - 26 Aug 2007 01:01 GMT

Good evening:

Thanks in advance for your help
John
______________________________________________________

Sub MoveData()
'
' MoveData Macro
' Macro recorded 8/25/2007 by John Donadio
'
' Keyboard Shortcut: Ctrl+z
'
Range("A12:L12").Select
Selection.Copy
Sheets("Activity").Select
Range("A1").Select
Selection.End(xlDown).Select
Range("A26").Select ** I need this to go to the a blank
row rather than returning to A26**
ActiveSheet.Paste
Range("A1").Select
Sheets("Data Sheet").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

Reply to this Message

Dave Peterson - 26 Aug 2007 01:27 GMT

Option Explicit
Sub MoveData2()

Dim FromRng as range
Dim ToRng as range

with workSheets("Data Sheet")
set fromrng = .range("A12:L12")
end with

with worksheets("Activity")
set torng = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

fromrng.copy _
destination:=torng

fromrng.clearcontents

End Sub

And I don't think I'd use ctrl-z as my keyboard shortcut. That's the shortcut
for Edit|Undo.

How about ctrl-shift-Z.

> Good evening:
>
[quoted text clipped - 22 lines]
> Selection.ClearContents
> End Sub

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 26 Aug 2007 01:35 GMT

This will find the first blank in column A

Sub MoveData()
Sheets("Data Sheet").Select
ActiveSheet.Range("A12:L12").Copy Destination:= _
Sheets("Activity").Cells.End(xlDown) _
.Offset(1, 0)
Range("A1").ClearContents
End Sub

No need for all that selecting, copying and pasting that Excel records.

To find the first blank cell at the bottom of the column and copy to that range
use this......

Sub MoveData()
Sheets("Data Sheet").Select
ActiveSheet.Range("A12:L12").Copy Destination:= _
Sheets("Activity").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
Range("A1").ClearContents
End Sub

Gord Dibben MS Excel MVP

>Good evening:
>
[quoted text clipped - 22 lines]
> Selection.ClearContents
>End Sub

Reply to this Message

Rate this thread: