Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming |