VBA: Copy column data and paste it on the next available blank cell

Usually_Wrong

New Member
Joined
Oct 6, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hello!

So I'm hoping to do a couple of things on this workbook. I'm going to try and be as clear as I can, but please bear with me.

Task #1: Copy a specific column and paste it at the very bottom of the table. In this scenario, the first column I'd like to do this with is Column A. Copy paste values from column A and add it to the very bottom of the table - still located in column A.

VBA Code:
Sub CopyPaste()
        Range("Table1[Employee ID]").Copy
    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub

Finished product once the macro is run -
1633559923322.png



Task #2: Do the same thing for columns C and E.
At this point, this is where I'm drawing a blank. I would like to copy the data from Column C, paste it under C11, and do the same for column E. However, if I use the formula from above, it makes it so that the copied values on column C are instead pasted on C20, the last available row - column E's copied data would then be pasted after the column C data.

I tried to search for solutions, but I don't believe I've come across it just yet.

The hope is I get a final product like this -
1633561206093.png
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,769
Office Version
  1. 2010
Platform
  1. Windows
Would you mind explaining to me how this formula picks which cells to copy?
As my demonstration does not use any formula but just picks up data from the table body range according to a column index …​
what would the code look like if, say, I'd like to copy the values from E2:E to F instead?
To copy columns E & F this is the way with some safety :​
VBA Code:
Sub Demo2EF()
    Dim R&
    With [Sheet1!A1].ListObject
        R = .ListRows.Count
        If R Then .Range(R, 5)(3).Resize(R, 2).Value = .DataBodyRange.Columns("E:F").Value
    End With
End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,032
Messages
5,767,750
Members
425,430
Latest member
corinaas

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
Top