Copying/Inserting Column

happyhungarian

Active Member
Joined
Jul 19, 2011
Messages
254
Office Version
  1. 365
Platform
  1. Windows
Hi, I'm using the following code to insert a new column at the end of existing columns. What I was hoping to do is prior to inserting the new column, it would copy what's currently the last column and either insert a new copy of that column at the end OR first insert the new column then paste the format/content of the neighboring column to it.

Sub INSERT_COL()


Dim LastCol As Long


LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column


Columns(LastCol).Insert Shift:=xlToRight


End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Thank you! I literally just came up with the following solution as well:

Sub AddTask()
Dim LC As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(LC).Copy
Cells(1, LC + 1).PasteSpecial Paste:=xlPasteFormulas
Cells(1, LC + 1).PasteSpecial Paste:=xlPasteFormats
End Sub
 
Upvote 0
Another question on this :(... I need code that would copy data from a range (say A1:B10) and append it to the last row of a table (let's name it "Table1") in the table's first two columns (named Column1 and Column2). I was going to use the same method as up above but the problem I am having is that this is being done on the same worksheet that the Copying/Inserting column is happening; therefore, I would have a problem with the "LC + 1" logic interferring with the two separate operations (the inserting of a column and the copying pasting data to a different table)... i reeeeeally hope i made sense there
 
Upvote 0
Tried answering my own question with this: I shifted the data for the table down a row so that I can use the second row to locate the first column of the table: I think it's almost there but for some reason it's no finding the last row.

Sub AddTask()
Dim LC As Long
Dim LR As Long


Range("B12:C50").Copy


LC = Cells(2, Columns.Count).End(xlToLeft).Column
LR = Cells(Rows.Count, LC).End(xlToLeft).Column


Cells(LR + 1, LC).PasteSpecial Paste:=xlPasteValues




End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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