Cannot paste to a specific column using VBA

[Smiddy243]

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

 

[DanteAmor]

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

<tbody>
</tbody>

exactly which columns do you want to copy and where do you want to paste them?

 

[Smiddy243]

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

Good morning DanteAmor!

Thank you for your reply/question. I have about 50 columns. Most cells have data in them but many do not. I have added your case statement for these columns and they are going into the correct columns.

I have columns such as Address, City and Province (these would ALWAYS have data) but as you can see in the Zip Code above (left table), the cell could be null or empty as it may not be known or just not entered. The code provided doesn't seem to be entering that null/empty value where needed thus the result in the table on the right hand side. ie 2nd Avenue should be pasted with a null value in the Zip Code...Not the zip code from 5th Avenue. I will have several columns out of these 50 that would have this same condition.

I hope I've explained well... And THANK YOU for helping me with this. I sincerely appreciate the time and effort you are giving to me.

 

[DanteAmor]

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

Good morning DanteAmor!

Thank you for your reply/question. I have about 50 columns. Most cells have data in them but many do not. I have added your case statement for these columns and they are going into the correct columns.

I have columns such as Address, City and Province (these would ALWAYS have data) but as you can see in the Zip Code above (left table), the cell could be null or empty as it may not be known or just not entered. The code provided doesn't seem to be entering that null/empty value where needed thus the result in the table on the right hand side. ie 2nd Avenue should be pasted with a null value in the Zip Code...Not the zip code from 5th Avenue. I will have several columns out of these 50 that would have this same condition.

I hope I've explained well... And THANK YOU for helping me with this. I sincerely appreciate the time and effort you are giving to me.

50 Columns? That changes some things.

In the code, change the letter "H" to the letter of the column that will always have a data.

Update the other columns as appropriate.

Private Sub CommandButton1_Click()
    Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, lr As Long
    Set sh1 = Sheets("Deal Information")
    Set sh2 = Sheets("Province")
    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"
                lr = sh2.Range("[SIZE=3][COLOR=#ff0000][B]H[/B][/COLOR][/SIZE]" & Rows.Count).End(xlUp).Row + 1
                sh2.Range("[COLOR=#0000ff]A[/COLOR]" & lr).Value = sh1.Cells(i, "[COLOR=#0000ff]A[/COLOR]").Value
                sh2.Range("[COLOR=#0000ff]B[/COLOR]" & lr).Value = sh1.Cells(i, "[COLOR=#0000ff]B[/COLOR]").Value
                '...
                'MORE COLUMNS
                '...
                sh2.Range("[COLOR=#0000ff]H[/COLOR]" & lr).Value = sh1.Cells(i, "[COLOR=#0000ff]C[/COLOR]").Value
                'ETC...
        End Select
    Next
    MsgBox "Done"
End Sub

 

[Smiddy243]

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

Good morning DanteAmor!

I just wanted to let you know that I figured out the code and everything works perfect - I just wanted to thank you very much for your assistance. I could not have done this without you.

This question I posted was step 1 of what I am trying to do. I will post another thread for my 2nd part.

I will name it "Help with Copy then paste to specific cells where criteria matches"

I hope you see it and can help me once again.

Thank you once more!

Smiddy

 

[DanteAmor]

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

Good morning DanteAmor!

I just wanted to let you know that I figured out the code and everything works perfect - I just wanted to thank you very much for your assistance. I could not have done this without you.

This question I posted was step 1 of what I am trying to do. I will post another thread for my 2nd part.

I will name it "Help with Copy then paste to specific cells where criteria matches"

I hope you see it and can help me once again.

Thank you once more!

Smiddy

I'm glad to help you. I appreciate your kind comments.