Macro to copy and paste
 

Here is what I would like to assign a macro to do:

1. Copy information in row 1, columns B:N.
2. Paste that information in the first available row, starting in row 5.
3. Clear info in row 1, for new data entry
4. Repeat steps but this time paste new data directly below the row where
data was previously entered.

This question was adequately answered for me yesterday but I can't seem to
find it on the discussion board to copy the instructions given to me.

Macro to copy and paste
 

Something like this should do...

Sub CopyStuff() 'Run Me
Dim rngCopy As Range
Static rngPaste As Range

Set rngCopy = Range("B1:N1")
If rngPaste Is Nothing Then
Set rngPaste = LastCell
Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row,
"N"))
End If
Set rngPaste = rngPaste.Offset(1, 0)
rngPaste.Value = rngCopy.Value
rngCopy.ClearContents
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


"Taylor" wrote:

Here is what I would like to assign a macro to do:

1. Copy information in row 1, columns B:N.
2. Paste that information in the first available row, starting in row 5.
3. Clear info in row 1, for new data entry
4. Repeat steps but this time paste new data directly below the row where
data was previously entered.

This question was adequately answered for me yesterday but I can't seem to
find it on the discussion board to copy the instructions given to me.

Macro to copy and paste
 

try this
Sub Moverow()
lr = Cells(Rows.Count, "b").End(xlUp).Row + 1
With Range(Cells(1, "b"), Cells(1, "bn"))
..Copy Cells(lr, "b")
..ClearContents
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Taylor" wrote in message
...
Here is what I would like to assign a macro to do:

1. Copy information in row 1, columns B:N.
2. Paste that information in the first available row, starting in row 5.
3. Clear info in row 1, for new data entry
4. Repeat steps but this time paste new data directly below the row where
data was previously entered.

This question was adequately answered for me yesterday but I can't seem to
find it on the discussion board to copy the instructions given to me.

Macro to copy and paste
 

There's a syntax error at ..Copy Cells (lr, "b")

"Don Guillett" wrote:

try this
Sub Moverow()
lr = Cells(Rows.Count, "b").End(xlUp).Row + 1
With Range(Cells(1, "b"), Cells(1, "bn"))
..Copy Cells(lr, "b")
..ClearContents
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Taylor" wrote in message
...
Here is what I would like to assign a macro to do:

1. Copy information in row 1, columns B:N.
2. Paste that information in the first available row, starting in row 5.
3. Clear info in row 1, for new data entry
4. Repeat steps but this time paste new data directly below the row where
data was previously entered.

This question was adequately answered for me yesterday but I can't seem to
find it on the discussion board to copy the instructions given to me.

Macro to copy and paste
 

Also a syntax error he
Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row,

"Jim Thomlinson" wrote:

Something like this should do...

Sub CopyStuff() 'Run Me
Dim rngCopy As Range
Static rngPaste As Range

Set rngCopy = Range("B1:N1")
If rngPaste Is Nothing Then
Set rngPaste = LastCell
Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row,
"N"))
End If
Set rngPaste = rngPaste.Offset(1, 0)
rngPaste.Value = rngCopy.Value
rngCopy.ClearContents
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


"Taylor" wrote:

Here is what I would like to assign a macro to do:

1. Copy information in row 1, columns B:N.
2. Paste that information in the first available row, starting in row 5.
3. Clear info in row 1, for new data entry
4. Repeat steps but this time paste new data directly below the row where
data was previously entered.

This question was adequately answered for me yesterday but I can't seem to
find it on the discussion board to copy the instructions given to me.

Macro to copy and paste
 

The text wrapped in the browser...
Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row, "N"))

--
HTH...

Jim Thomlinson


"Taylor" wrote:

Also a syntax error he
Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row,

"Jim Thomlinson" wrote:

Something like this should do...

Sub CopyStuff() 'Run Me
Dim rngCopy As Range
Static rngPaste As Range

Set rngCopy = Range("B1:N1")
If rngPaste Is Nothing Then
Set rngPaste = LastCell
Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row,
"N"))
End If
Set rngPaste = rngPaste.Offset(1, 0)
rngPaste.Value = rngCopy.Value
rngCopy.ClearContents
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


"Taylor" wrote:

Here is what I would like to assign a macro to do:

1. Copy information in row 1, columns B:N.
2. Paste that information in the first available row, starting in row 5.
3. Clear info in row 1, for new data entry
4. Repeat steps but this time paste new data directly below the row where
data was previously entered.

This question was adequately answered for me yesterday but I can't seem to
find it on the discussion board to copy the instructions given to me.

Macro to copy and paste
 

Somehow there are TWO dots in the with statement. Delete ONE of them.
.Copy Cells(lr, "b")
.ClearContents
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Taylor" wrote in message
...
There's a syntax error at ..Copy Cells (lr, "b")

"Don Guillett" wrote:

try this
Sub Moverow()
lr = Cells(Rows.Count, "b").End(xlUp).Row + 1
With Range(Cells(1, "b"), Cells(1, "bn"))
..Copy Cells(lr, "b")
..ClearContents
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Taylor" wrote in message
...
Here is what I would like to assign a macro to do:

1. Copy information in row 1, columns B:N.
2. Paste that information in the first available row, starting in row
5.
3. Clear info in row 1, for new data entry
4. Repeat steps but this time paste new data directly below the row
where
data was previously entered.

This question was adequately answered for me yesterday but I can't seem
to
find it on the discussion board to copy the instructions given to me.