Copy paste column

**ell_** · 04-11-2019, 09:26 PM

Hi, all

I have this one file where I need to copy paste the filled cells in column A in a table from Sheet Raw data to column A in MB.

This is quite simple though hence I already wrote one short code.

But it shows application-defined or object-defined error.

Sub main()
    Dim r As Range

    Set r = ActiveWorkbook.Sheets("Raw Data").Range("A")
    Do While r.Value <> ""
        ActiveWorkbook.Sheets("MB").Range("A").Value = r.Value
        Set r = r.Offset(1)
    Loop
End Sub

Is it because I'm dealing with a table instead of cells? Hope anyone can enlighten me. Thanks!

**Richard Buttrey** · 04-11-2019, 09:51 PM

If I understand the request why not just use the single line of code

Sheet1.Range("A1").CurrentRegion.Offset(1, 0).Copy Destination:=Sheet2.Range("A2")

**tax112** · 04-11-2019, 09:56 PM

Try:

Sub Test()
With Sheets("Raw data")
   .Range("A2", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Copy Sheets("MB").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
End With
End Sub

**ell_** · 04-11-2019, 10:21 PM

Hi, tax112

Your code can be ran but nothing came out, not sure why though? A pop-out box came out which says Do you want to replace the content of destination cells although it's already empty.

Update: and now it currently shows error: subscript out of range

Hi, Richard

Your code shows error: Object required, not sure why either.

**Richard Buttrey** · 04-12-2019, 04:22 AM

Originally Posted by ell_

Hi, tax112

Your code can be ran but nothing came out, not sure why though? A pop-out box came out which says Do you want to replace the content of destination cells although it's already empty.

Update: and now it currently shows error: subscript out of range

Hi, Richard

Your code shows error: Object required, not sure why either.

You'll need to upload the workbook in which you've entered the code I gave you and explain exactly what you do. Have you changed something else perhaps.

Add an additional line of code before the code I gave of

Application.DisplayAlerts = False

That will presumably fix it but it would be good to know why you get the warning in the first place.

**tax112** · 04-11-2019, 11:25 PM

Please press the button

**ell_** · 04-14-2019, 08:00 PM

Hi, tax112

Thank you so much. It works now.

Hi Richard,

Thank you for the solution, it works as well.

Copy paste column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy paste column

Re: Copy paste column

Re: Copy paste column

Re: Copy paste column

Re: Copy paste column

Re: Copy paste column

Re: Copy paste column

Thread Information

Users Browsing this Thread

Similar Threads

Problem adding column to ListObject, naming it and attempt to Copy or Copy/Paste into it?

EASYHELP**copy paste value in column to another column, if blank look at another column

How to Match a column with another sheet's column and copy paste the Entire Row&Column aft

How do I make the copy/paste of one column contingent on the copy/paste of another?

Foruma to query 1 column then copy the next column across and paste to a new column

Vba - find next empty column, insert column, copy paste values & copy paste formulas

Save column J only using copy/paste & temporary copy

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions