Code for dragging the last column

[Gaurav]

Hi All,

I am looking for a Macro that unprotects the sheet using a password, drags
the last populated column to the next column and then protects the sheet
using the same password.

Thanks in advance.
Gaurav

 

[Ian]

Sub test()
Worksheets("Sheet1").Unprotect password:="test"
myrange = Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Column
Cells(1, myrange).EntireColumn.Insert (xlRight)
Worksheets("Sheet1").Protect password:="test"
End Sub

There's no need to drag the contents to the next column as the Insert
statement includes the term xlRight (ie insert these cells and move the
displaced cells to the right).

Ian

 

[Gaurav]

I am getting a pop saying "subscript out of range".

 

[Gaurav]

Sorry I did not change the sheet name. I have changed the sheet name and now
i am getting error 400.

 

[Gaurav]

Hey now i can see that it is doing something but no changes are being made.

It is a running report that picks up data from a different sheet. First 3
cells value will remain the same but after that all the columns have
references to the other sheet. Now i want this macro to drag the entire
column just like i would do manually so that formulas adjust themselves.

The last populated column will remain there. this macro will change the next
column from blank to populated with the same values/formulas and formatting
as the one on its left.

Thanks

 

[Ian]

If I understand, you mean you want the existing columns to remain as they
are, but you want a new column at the end with the same formulae as the last
column.

Amending the previous code gives:

Sub test()
Worksheets("Sheet1").Unprotect password:="test"
myrange = Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Column
Cells(1, myrange).EntireColumn.Copy
Cells(1, myrange + 1).PasteSpecial (xlPasteFormulas)
Worksheets("Sheet1").Protect password:="test"
End Sub

Ian