Cannot paste to a specific column using VBA
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Cannot paste to a specific column using VBA

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cannot paste to a specific column using VBA

    Hello folks;

    My very first post here...Using Excel 2016 with VBA.
    I have created some code that will copy specific data based upon certain criteria. I'm not very good at VBA so it may not be the most efficient but I'm 98% there...the criteria works great!

    I cannot for the life of me paste the data where I want. It is pasting to Column A - I would like it to paste into Column H. I am sure it's the last line...but when I enter Range or even just PasteSpecial Paste:=xlPasteValues, I get the Run-Time error 1004 "Application-defined or Object-defined error".

    Here's what I've come up with so far....

    Private Sub CommandButton1_Click()
    a = Worksheets("Deal Information").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 8 To a
    If Worksheets("Deal Information").Cells(i, "J").Value = "New Brunswick" _
    Or Worksheets("Deal Information").Cells(i, "J").Value = "NB" _
    Or Worksheets("Deal Information").Cells(i, "J").Value = "Nova Scotia" _
    Or Worksheets("Deal Information").Cells(i, "J").Value = "NS" _
    Or Worksheets("Deal Information").Cells(i, "J").Value = "Prince Edward Island" _
    Or Worksheets("Deal Information").Cells(i, "J").Value = "PEI" _
    Or Worksheets("Deal Information").Cells(i, "J").Value = "Newfoundland" _
    Or Worksheets("Deal Information").Cells(i, "J").Value = "Newfoundland and Labrador" _
    Or Worksheets("Deal Information").Cells(i, "J").Value = "NL" _
    Then
    Worksheets("Deal Information").Cells(i, "C").Copy
    Worksheets("Province").Activate
    b = Worksheets("Access AR").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Province").Cells(b + 1, 1).Select
    Worksheets("Province").Paste
    End If
    Next
    Application.CutCopyMode = False
    End Sub

    Thanks folks - sincerely...I've worked on this for hours so decided to reach-out.

    Smiddy

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    Sorry everyone - made a typo....

    the line that starts with "b=" should say Province (Not Access AR)....sorry for the confusion...

    Smiddy

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,729
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    Hi, welcome to the forum

    It can be simplified in this way.

    Code:
    Private Sub CommandButton1_Click()
        Dim sh1 As Worksheet, i As Long
        Set sh1 = Sheets("Deal Information")
        For i = 8 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
            Select Case sh1.Cells(i, "J").Value
                Case "New Brunswick", "NB", "Nova Scotia", "NS", "Prince Edward Island", _
                     "PEI", "Newfoundland", "Newfoundland and Labrador", "NL"
                    Sheets("Province").Range("H" & Rows.Count).End(xlUp)(2).Value = sh1.Cells(i, "C").Value
            End Select
        Next
        MsgBox "Done"
    End Sub
    Let me know if you have any questions.
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,560
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    Code:
    Worksheets("Province").Cells(b + 1, "H").Select

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,729
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    Quote Originally Posted by footoo View Post
    Code:
    Worksheets("Province").Cells(b + 1, "H").Select

    But to paste in column H, you must also count the last row of column H, if there are blank cells in A, in that case:

    Code:
        Then
            Worksheets("Deal Information").Cells(i, "C").Copy
            Worksheets("Province").Activate
            b = Worksheets("Province").Cells(Rows.Count, "H").End(xlUp).Row
            Worksheets("Province").Cells(b + 1, "H").PasteSpecial Paste:=xlPasteValues
           
        End If
    Regards Dante Amor

  6. #6
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,560
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    Not necessarily. There might be blank cells in column H (but never in column A), so column A is used to find the next blank row.

  7. #7
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,560
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    Quote Originally Posted by footoo View Post
    Not necessarily. There might be blank cells in column H (but never in column A), so column A is used to find the next blank row.
    Forget that. Cannot be the case because of the loop

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,729
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    I would like it to paste into Column H
    then the column with data is the H
    Regards Dante Amor

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    Hello DanteAmor;

    I love this code! It is simple and fast! But I have a problem. I am copying more than 1 column - Some of the values in these other columns are blank. I hope I can illustrate my problem....First is what I need to see, second is what your code is doing....

    Apparently, the code is ignoring the blank cells and populates it with the next non-blank cell...so as you can see in my second table, the zip codes in red are now misaligned and have basically moved up to fill that first blank cell. Is there some way to evaluate these blank cells and copy/paste them even if blank?


    What we need to see What we get
    Street Zip Code Street Zip Code
    1st Avenue 90210 1st Avenue 90210
    2nd Avenue 2nd Avenue 72140
    5th Avenue 72140 5th Avenue 86830
    10th Avenue 86830 10th Avenue 46780
    11th Avenue 11th Avenue
    22nd Avenue 46780 22nd Avenue

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot paste to a specific column using VBA...real Head Scratcher...

    Hello DanteAmor;

    I love this code! It is simple and fast! But I have a problem. I am copying more than 1 column - Some of the values in these other columns are blank. I hope I can illustrate my problem....First is what I need to see, second is what your code is doing....

    Apparently, the code is ignoring the blank cells and populates it with the next non-blank cell...so as you can see in my second table, the zip codes in red are now misaligned and have basically moved up to fill that first blank cell. Is there some way to evaluate these blank cells and copy/paste them even if blank?



    What we need to see What we get
    Street Zip Code Street Zip Code
    1st Avenue 90210 1st Avenue 90210
    2nd Avenue 2nd Avenue 72140
    5th Avenue 72140 5th Avenue 86830
    10th Avenue 86830 10th Avenue 46780
    11th Avenue 11th Avenue
    22nd Avenue 46780 22nd Avenue

Some videos you may like

User Tag List

Tags for this Thread

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
  •