Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste problem (V.B. 6.3)

    Hi, I'm starting to use Macros. And I have a problem, I cant paste and I dont know what I'm doing wrong. I would appreciate your help. I first used the "Rec utility" and then I tried to fix it manually because it didn't work.
    I have information in a sheet and I dont know how long it is. And I want to paste it in another sheet.
    Range("AE2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Worksheets("Resumen (Barras)").Select
    Range("a5").Select

    Then I dont know how to paste, because I put Selection.Paste and it doesnt work.
    I also tried with Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False (the Rec did it)

    I dont have much idea about VB as you can see.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste problem (V.B. 6.3)

    Welcome to Woody's Lounge!

    Paste is a method of the Worksheet object, not of the Range object. So you could use

    ActiveSheet.Paste Destination:=Range("A5")

    Although the macro recorder is a good way to get a feel for what kind of instructions you can use, it often doesn't generate elegant code. In this particular situation, copying and pasting can be accomplished using only one instruction:

    Range(Range("AE2"), Range("AE2").End(xlDown)).Copy Destination:=Worksheets("Resumen (Barras)").Range("A5")

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Paste problem (V.B. 6.3)

    Something like:
    <pre>activesheet.range("AE2",range("AE2").End(xlDo wn)).copy _
    destination:=worksheets("Resumen (Barras)").range("A5")
    </pre>

    should work.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Apr 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste problem (V.B. 6.3)

    I don't know what happens, but I tried the 3 options that u gave me and the didn't work the HansV solution always have the error 1004 and the explanation is spanish (sorry), if u want I can translate it. And the Rory solution gives me an error in the VB editor that it was expecting an expression and it select the ":="
    Thanks, and sorry for my english

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste problem (V.B. 6.3)

    Could you attach a copy of your workbook to a reply? You can replace sensitive data with dummy data, and remove sheets that are not relevant to the problem.

  6. #6
    New Lounger
    Join Date
    Apr 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste problem (V.B. 6.3)

    Here I upload a file with the idea of my problem. But sometimes work sometimes it doesnt.
    The problem is with the paste, like i've already told you.
    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste problem (V.B. 6.3)

    The code should work OK if Hoja1 is the active worksheet, but it will fail if another worksheet happens to be the active worksheet when the macro is run. You could insert the following line at the beginning of the macro to ensure that Hoja1 is the active worksheet:

    Worksheets("Hoja1").Select

    If your goal is to copy unique values from Hoja1 to Resumen (Barras), you can use Advanced Filter - see the attached version. You need to create field names for the data, I simply used "F".

    Sub Problem()
    Worksheets("Hoja1").Range("A1").CurrentRegion.Adva ncedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Worksheets("Resumen (Barras)").Range("A4"), _
    Unique:=True
    End Sub

  8. #8
    New Lounger
    Join Date
    Apr 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste problem (V.B. 6.3)

    Thank you, I will use the Adv Filter, I didn't know it.
    Anyway, I don't know why I had problems with the paste, sometimes worked and other didnt. And I tried putting the name of the sheet. But I will use the Filter, it seems easyer.
    Thank you for your time

Posting Permissions

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