Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

Macro to copy and paste

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Taylor - 15 May 2008 15:51 GMT
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.
Jim Thomlinson - 15 May 2008 16:39 GMT
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
Signature

HTH...

Jim Thomlinson

> Here is what I would like to assign a macro to do:
>
[quoted text clipped - 6 lines]
> 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.
Taylor - 15 May 2008 17:08 GMT
Also a syntax error here:          
Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row,

> Something like this should do...
>
[quoted text clipped - 52 lines]
> > 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.
Jim Thomlinson - 15 May 2008 17:36 GMT
The text wrapped in the browser...
Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row, "N"))

Signature

HTH...

Jim Thomlinson

> Also a syntax error here:          
> Set rngPaste = Range(Cells(rngPaste.Row, "B"), Cells(rngPaste.Row,
[quoted text clipped - 55 lines]
> > > 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.
Don Guillett - 15 May 2008 16:46 GMT
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
Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Here is what I would like to assign a macro to do:
>
[quoted text clipped - 6 lines]
> 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.
Taylor - 15 May 2008 17:05 GMT
There's a syntax error at ..Copy Cells (lr, "b")

> try this
> Sub Moverow()
[quoted text clipped - 14 lines]
> > 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.
Don Guillett - 15 May 2008 17:43 GMT
Somehow there are TWO dots in the with statement. Delete ONE of them.
 .Copy Cells(lr, "b")
 .ClearContents
Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> There's a syntax error at ..Copy Cells (lr, "b")
>
[quoted text clipped - 19 lines]
>> > to
>> > find it on the discussion board to copy the instructions given to me.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.