Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

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

    Default

    Well here is my problem. I want to copy a value from a worksheet and paste it into find on a different worksheet. I want to find the value on the second worksheet, copy the three cells next to it and change to the first worksheet and paste them into it.

    Everything seems to work fine except when I past my value into the find. I want to be able to loop it but when I run it twice it finds the original value all the time instead of the new copied one?!?!?

    If anyone can see what is wrong here please let me know it would be greatly appreciated.


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

    Default

    Could you post your code? That would help.

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

    Default

    I guess I should have thought of that. Here is the code to my question:

    ActiveCell.Offset(0, -3).Range("A1").Select
    Selection.Copy
    Windows("Test2.xls").Activate
    Cells.Find(What:="714491", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
    ActiveCell.Offset(0, 1).Range("A1:C1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Windows("Test1.xls").Activate
    ActiveCell.Offset(0, 3).Range"A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range"A1").Select



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

    Default

    On 2002-04-12 10:46, Man of Action wrote:
    I guess I should have thought of that. Here is the code to my question:

    ActiveCell.Offset(0, -3).Range("A1").Select
    Selection.Copy
    Windows("Test2.xls").Activate
    Cells.Find(What:="714491", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
    ActiveCell.Offset(0, 1).Range("A1:C1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Windows("Test1.xls").Activate
    ActiveCell.Offset(0, 3).Range"A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range"A1").Select


    Well the part

    Cells.Find(What:="714491", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

    is always going to look for 714491, which I assume is the value of A1.

    You need to use

    ActiveCell.Offset(0, -3).Range("A1").Select
    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:C1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Windows("Test1.xls").Activate
    ActiveCell.Offset(0, 3).Range"A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range"A1").Select

    EDIT: What range are you using that containns the values you want to find? I could try to help with the looping part.

    Also, I don't understand why you have

    ActiveCell.Offset(0, -3).Range("A1").Select

    instead of just

    Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Select

    [ This Message was edited by: Cosmos75 on 2002-04-12 12:10 ]

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Rewrite your code so you don't select anything. Is Test1.xls the workbook where you have this procedure, or are you transferring a value from test2 to test1 after you have copied the first part (controlled from a 3rd workbook)?

    In any event, I think the following will do what you want...

    ------------------
    Sub test()
    Dim FoundCell As String

    FoundCell = Workbooks("Test2.xls").Sheets("Sheet1").Cells.Find(What:="714491", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Address(False, False)

    ActiveCell.Offset(0, -3).Copy Workbooks("Test2.xls").Sheets("Sheet1").Range(FoundCell).Offset(0, 1).Resize(, 3)

    ActiveCell = Workbooks("Test2.xls").Sheets("Sheet1").Range(FoundCell).Value
    End Sub
    ----------------

    HTH,
    Jay

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

    Default

    Jay,

    I think Man of Action record a macro by way of selecting a cell, copying the value and then choosing Find and pasting the value in the find inputbox. When you record a macro it enters the value copied but doesn't take into account if the value changes. Whatever value was copied originally when the macro was recorded is the one that will always be used.

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

    Default

    Thanks for your help guys.

    Cosmos,

    I tried that change you suggested but when I run the macro the find does not work. It doesn't look for anything?

    Confused,
    M of A


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

    Default

    Kinda odd, it worked for me.

    Try opening the VB Editor and running through the macro line by line by using F8. Let me know what's where the error occurs.

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

    Default

    My post showed up as

    ActiveCell.Offset(0, -1).Range("A1").Select
    Windows("Test1.xls").Activate
    ActiveCell.Offset(0, 3).Range"A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range"A1").Select

    Should be

    ActiveCell.Offset(0, -1).Range("A1").Select
    Windows("Test1.xls").Activate
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select

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

    Default

    I got it working. Thanks.

    If it can't find the value it just craps out. Is there a way to continue?


Some videos you may like

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
  •