Copying/Inserting Column

happyhungarian

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

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

happyhungarian

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

happyhungarian

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

happyhungarian

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

Watch MrExcel Video

Forum statistics

Threads
1,108,809
Messages
5,525,002
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top