Using copy and paste in macro..... - Page 2
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Using copy and paste in macro.....

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Try this...


    On Error GoTo ErrorHandler
    'Insert code

    -code-

    'The following line will halt the macro before the error handler
    'is reached.
    Exit Sub
    ErrorHandler:
    Message = MsgBox("No Match Found")

    Edit: If it works, would you mind posting the code here so that I can see if there's anything I can change to make it work better or neater. THANKS!

    [ This Message was edited by: Cosmos75 on 2002-04-15 13:42 ]

  2. #12
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Could you help me with a loop for about 3000 rows?


  3. #13
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure, I'll try.

    First.

    What columns are used in Test1.xls and Test2.xls?

    How is the data organized?

  4. #14
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't worry about it buddy I figured it out.
    Thanks for all your help.

    Here is the code I ended up using.

    Sub Copy_Name()
    '
    ' Copy_Name Macro
    '
    On Error GoTo Errorhandler
    Selection.Copy
    FindValue = Selection.Value
    Windows("Test2.xls").Activate
    Cells.Find(What:=FindValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Main.xls").Activate
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, -2).Range("A1").Select

    Exit Sub
    Errorhandler:
    'Message = MsgBox("No Match Found")
    Windows("Main.xls").Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
    End Sub

    It worked all right. Might make you freak out a bit cuz the screen flips back and forth from two different windows when you loop it. Sorta funny.



  5. #15
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Glad I could be of help to someone.

    Where's the loop part?

    You can stop of from blipping (switching between the two by using this at the beginning of your code.

    ' Turn off screenupdating:
    Application.ScreenUpdating = False

    and then put this at the end of your code

    ' Turn off screenupdating:
    Application.ScreenUpdating = True

    It'll help speed up the code somewhat.

    [ This Message was edited by: Cosmos75 on 2002-04-15 15:13 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com