Copying/Inserting Column

happyhungarian

Board Regular
Joined
Jul 19, 2011
Messages
247
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
Duh... nevermind... had to change it to

LR = Cells(Rows.Count, LC).End(xlUp).Row
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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