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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
Re: Cannot paste to a specific column using VBA...real Head Scratcher...

Code: 
[COLOR=#333333]Worksheets("Province").Cells(b + 1, [/COLOR][COLOR=#ff0000]"H"[/COLOR][COLOR=#333333]).Select[/COLOR]
 
Upvote 0
Re: Cannot paste to a specific column using VBA...real Head Scratcher...

footoo said:
Code: 
[COLOR=#333333]Worksheets("Province").Cells(b + 1, [/COLOR][COLOR=#ff0000]"H"[/COLOR][COLOR=#333333]).Select[/COLOR]
Click to expand...


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, [COLOR=#0000ff]"H"[/COLOR]).End(xlUp).Row
        Worksheets("Province").Cells(b + 1, [COLOR=#0000ff]"H"[/COLOR]).[COLOR=#0000ff]PasteSpecial Paste:=xlPasteValues[/COLOR]
       
    End If
 
Upvote 0
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.
 
Upvote 0
Re: Cannot paste to a specific column using VBA...real Head Scratcher...

footoo said:
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.
Click to expand...
Forget that. Cannot be the case because of the loop
 
Upvote 0
Re: Cannot paste to a specific column using VBA...real Head Scratcher...

I would like it to paste into Column H
Click to expand...

then the column with data is the H
 
Upvote 0
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?


[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]22nd Avenue[/TD]
[TD="align: center"]46780[/TD]
[TD][/TD]
[TD]22nd Avenue[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?



[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]
 
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
218
mumps
mumps
S
  • Question Question
VBA Copy/Paste from different workbooks
Replies
1
Views
365
NdNoviceHlp
NdNoviceHlp
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
622
myall_blues
myall_blues
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
272
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
72
Denny57
D

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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