Cannot paste to a specific column using VBA

S

Smiddy243

New Member
Joined
Jul 12, 2019
Messages
8
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
 
Sort by date Sort by votes
Re: Cannot paste to a specific column using VBA...real Head Scratcher...

Smiddy243 said:
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?



[TABLE="class: cms_table, width: 497"]
<tbody>[TR]
[TD="colspan: 2, align: center"]What we need to see[/TD]
[TD="align: center"][/TD]
[TD="colspan: 2, align: center"]What we get[/TD]
[/TR]
[TR]
[TD="align: center"]Street[/TD]
[TD="align: center"]Zip Code[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Street[/TD]
[TD="align: center"]Zip Code[/TD]
[/TR]
[TR]
[TD]1st Avenue[/TD]
[TD="align: center"]90210[/TD]
[TD][/TD]
[TD]1st Avenue[/TD]
[TD="align: center"]90210[/TD]
[/TR]
[TR]
[TD]2nd Avenue[/TD]
[TD][/TD]
[TD][/TD]
[TD]2nd Avenue[/TD]
[TD="align: center"]72140[/TD]
[/TR]
[TR]
[TD]5th Avenue[/TD]
[TD="align: center"]72140[/TD]
[TD][/TD]
[TD]5th Avenue[/TD]
[TD="align: center"]86830[/TD]
[/TR]
[TR]
[TD]10th Avenue[/TD]
[TD="align: center"]86830[/TD]
[TD][/TD]
[TD]10th Avenue[/TD]
[TD="align: center"]46780[/TD]
[/TR]
[TR]
[TD]11th Avenue[/TD]
[TD][/TD]
[TD][/TD]
[TD]11th Avenue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]22nd Avenue[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]46780[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]22nd Avenue[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
Re: Cannot paste to a specific column using VBA...real Head Scratcher...

Smiddy243 said:
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.
Click to expand...


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.

Code: 
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
 
Upvote 0
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
 
Upvote 0
Re: Cannot paste to a specific column using VBA...real Head Scratcher...

Smiddy243 said:
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
Click to expand...


I'm glad to help you. I appreciate your kind comments.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy paste columns based on matching header into another workbook
Replies
5
Views
325
mumps
mumps
S
  • Question Question
VBA Copy/Paste from different workbooks
Replies
1
Views
385
NdNoviceHlp
NdNoviceHlp
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
667
myall_blues
myall_blues
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
411
Peter_SSs
Peter_SSs
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
97
Denny57
D

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top