Copying/Inserting Column

happyhungarian

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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

happyhungarian

Board Regular
Joined
Jul 19, 2011
Messages
237
Office Version
  1. 365
Platform
  1. Windows
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
237
Office Version
  1. 365
Platform
  1. Windows
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
237
Office Version
  1. 365
Platform
  1. Windows
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:

happyhungarian

Board Regular
Joined
Jul 19, 2011
Messages
237
Office Version
  1. 365
Platform
  1. Windows
Duh... nevermind... had to change it to

LR = Cells(Rows.Count, LC).End(xlUp).Row
 
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,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

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