Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: COPY > PASTE (macro)

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why doesn't work this macro?
    I receive an error message on the line :
    Range("A5").PasteSpecial

    This is my macro :

    Sub Filter()
    currentworkbook = ActiveWorkbook.Name
    currentsheet = ActiveSheet.Name
    Columns("L:L").Select
    Selection.autofilter Field:=1, Criteria1:="1"
    Range("A5:" & Range("K65536").End(xlUp).Address).Copy
    Workbooks.OpenText Filename:="G:Mijn documentenMap1.xls"
    Sheets("Blad1").Select
    Range("A5").PasteSpecial
    Workbooks(currentworkbook).Sheets(currentsheet).Activate
    Selection.autofilter
    Range("A1").Select
    End Sub

    Many thanks for help.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmm...this code worked for me. Maybe theres something wrong with your cells? Is range a5 merged with any others in the workbook you opened?

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 01:27, robfo0 wrote:
    Hmm...this code worked for me. Maybe theres something wrong with your cells? Is range a5 merged with any others in the workbook you opened?
    Thanks for your answer.You are right.There was a merged with any others in the workbook.
    Maybe I may ask you another question: I want to clear all in the sheet Blad1,before pastespecial.Is this possible,or do I manuelly clear this before pastespecial?
    Many thanks for your time.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    insert this line when blad1 is selected
    Cells.ClearContents
    regards Tommy

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 02:58, Tommy Bak wrote:
    insert this line when blad1 is selected
    Cells.ClearContents
    regards Tommy
    Hi Tommy,

    Yes the cells are cleared before paste,but it doesn't work,maybe the copied cells have an interiorcolor.Those colors are not cleared.Can I add any code to also clear those colors,before paste?
    Thanks

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes Verluc, no problem.
    Cells.Clear

    regards Tommy

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 04:05, Tommy Bak wrote:
    Yes Verluc, no problem.
    Cells.Clear

    regards Tommy
    Hi Tommy,

    When I add the code and run the macro,I receive the error 1004
    "Methode PasteSpecial of class range ..."
    I have a yellow color on the line:
    Range("A1").PasteSpecial

    Have you any idea?
    Thanks

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 04:13, verluc wrote:
    On 2002-04-19 04:05, Tommy Bak wrote:
    Yes Verluc, no problem.
    Cells.Clear

    regards Tommy
    Hi Tommy,

    When I add the code and run the macro,I receive the error 1004
    "Methode PasteSpecial of class range ..."
    I have a yellow color on the line:
    Range("A1").PasteSpecial

    Have you any idea?
    Thanks
    Hi Tommy,

    Have you any idea about this erro message?
    Many thanks

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 05:58, verluc wrote:
    On 2002-04-19 04:13, verluc wrote:
    On 2002-04-19 04:05, Tommy Bak wrote:
    Yes Verluc, no problem.
    Cells.Clear

    regards Tommy
    Hi Tommy,

    When I add the code and run the macro,I receive the error 1004
    "Methode PasteSpecial of class range ..."
    I have a yellow color on the line:
    Range("A1").PasteSpecial

    Have you any idea?
    Thanks
    Hi Tommy,

    Have you any idea about this erro message?
    Many thanks
    Hi Tommy,

    When I delete the instruction Cells.Clear,then the macro works.
    With the instruction Cells.Clear,the macro doesn't work.

    Any Idea?

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi verluc.
    I've changed your macro a couple of places.
    Now it seems to run well.

    Sub Filter()
    currentworkbook = ActiveWorkbook.Name
    currentsheet = ActiveSheet.Name
    Columns("L:L").Select
    Selection.AutoFilter Field:=1, Criteria1:="1"
    Set kopi = Range("A5:" & Range("K65536").End(xlUp).Address)
    Workbooks.OpenText Filename:="G:\Mijn documenten\Map1.xls"
    Cells.Clear
    kopi.Copy
    Range("A5").Select
    ActiveSheet.Paste
    Workbooks(currentworkbook).Worksheets(currentsheet).Activate
    Selection.AutoFilter
    Range("A1").Select
    End Sub

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
  •