Trying to find the right code to copy a column from one worksheet and paste to another

Hi guys,

I'm trying to create a button that essentially copies a column of data from one worksheet (prime) and pastes this column of data in another worksheet (secondary).

However, I want to be able to keep pressing this button so that the newly pasted data in secondary is pasted into the next column. So whatever new inputs in the prime column will be copy pasted into a new column to the right of the previous copy paste. So far, I have this code below:

The problem is it only copies and pastes to one column...

Sub CollateProcurement()
    Dim NextRow As Long
    With Worksheets("Trend Analysis")
        iLastColumn = .Range("H7").End(xlToRight).Column
        Sheets("Procurement Pre Mobilisation").Range("I4").Copy .Range("H7")
        Sheets("Procurement Pre Mobilisation").Range("I5").Copy .Range("H8")
        Sheets("Procurement Pre Mobilisation").Range("I6").Copy .Range("H9")
        Sheets("Procurement Pre Mobilisation").Range("I7").Copy .Range("H10")
        Sheets("Procurement Pre Mobilisation").Range("I8").Copy .Range("H11")
        Sheets("Procurement Pre Mobilisation").Range("I9").Copy .Range("H12")
        Sheets("Procurement Pre Mobilisation").Range("I10").Copy .Range("H13")
        Sheets("Procurement Pre Mobilisation").Range("I11").Copy .Range("H14")
        Sheets("Procurement Pre Mobilisation").Range("I12").Copy .Range("H15")
        Sheets("Procurement Pre Mobilisation").Range("I13").Copy .Range("H15")
        Sheets("Procurement Pre Mobilisation").Range("I14").Copy .Range("H17")
        Sheets("Procurement Pre Mobilisation").Range("I15").Copy .Range("H18")
        Sheets("Procurement Pre Mobilisation").Range("I16").Copy .Range("H19")
        Sheets("Procurement Pre Mobilisation").Range("I17").Copy .Range("H20")
        Sheets("Procurement Pre Mobilisation").Range("I18").Copy .Range("H21")
        Sheets("Procurement Pre Mobilisation").Range("I19").Copy .Range("H22")
        Sheets("Procurement Pre Mobilisation").Range("I20").Copy .Range("H23")
        Sheets("Procurement Pre Mobilisation").Range("I21").Copy .Range("H24")
        Sheets("Procurement Pre Mobilisation").Range("I22").Copy .Range("H25")
    End with
End Sub

Any help would be greatly appreciated!

Thanks guys.

Kind regards,
M

Try this code -

Sub CollateProcurement()
Dim lcol As Long
With Worksheets("Trend Analysis")
    lcol = .Range("XFD7").End(xlToLeft).Column
    Worksheets("Procurement Pre Mobilisation").Range("I4:I22").Copy .Cells(7, lcol + 1)
End With
End Sub

Do you have any merged cells? Can you attach a sample file?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

If for example nothing has changed and I pressed the button then it still copies the data in column I from the first sheet and pastes it in the next blank column even if the numbers are exactly the same.

So do you want the macro to check if the data is the same from the previous copy paste and only if it is not, it should copy?

I copied the code to a module in the file and assigned it to the button and it worked. Did you do the same?

Does your file have any other codes? Did you right click on that cute button and say "Assign Macro" ?

Yeah, I mean. I've just copy pasted that code for another button for a different table beneath the first one and it works perfectly! It's just this first table that's being a pain...

Sub ColHS()
Dim lcol As Long
With Worksheets("Trend Analysis")
    lcol = .Range("XFD33").End(xlToLeft).Column
    Worksheets("Health and Safety Pre Mob").Range("G4:G9").Copy .Cells(33, lcol + 1)
End With
End Sub

The above works just like it should for this other table further down.

But the same code doesn't seem to want to work for the other table..

Sub CollateProcurement()
Dim lcol As Long
With Worksheets("Trend Analysis")
    lcol = .Range("XFD7").End(xlToLeft).Column
    Worksheets("Procurement Pre Mobilisation").Range("I4:I22").Copy .Cells(7, lcol + 1)
End With
End Sub

I can't seem to figure out the issue!

Thank you very much for your time with this by the way, I appreciate it

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

Do you have merged cells in the destination sheet? Can you upload a copy of the file?

Let me explain what the code does.

It goes to the extreme last column of the file which is XFD and then moves back to the last occupied column in that row. Do you have data in AD7 or even a space? It is catching AD7 as the last occupied column in row 7 and then populating AE7.

Yeah that will be better.

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.