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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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...

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


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...

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
 
Upvote 0
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
 
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?


What we need to seeWhat we get
StreetZip CodeStreetZip Code
1st Avenue902101st Avenue90210
2nd Avenue2nd Avenue72140
5th Avenue721405th Avenue86830
10th Avenue8683010th Avenue46780
11th Avenue11th Avenue
22nd Avenue4678022nd Avenue

<tbody>
</tbody>
 
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?



What we need to seeWhat we get
StreetZip CodeStreetZip Code
1st Avenue902101st Avenue90210
2nd Avenue2nd Avenue72140
5th Avenue721405th Avenue86830
10th Avenue8683010th Avenue46780
11th Avenue11th Avenue
22nd Avenue4678022nd Avenue

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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