Code for dragging the last column
 

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

Code for dragging the last column
 

Sub test()
Worksheets("Sheet1").Unprotect password:="test"
myrange = Worksheets("Sheet1").Cells.SpecialCells(xlCellType LastCell).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" wrote in message
...
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

Code for dragging the last column
 

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


"Ian" wrote in message
...
Sub test()
Worksheets("Sheet1").Unprotect password:="test"
myrange =
Worksheets("Sheet1").Cells.SpecialCells(xlCellType LastCell).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" wrote in message
...
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

Code for dragging the last column
 

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


"Ian" wrote in message
...
Sub test()
Worksheets("Sheet1").Unprotect password:="test"
myrange =
Worksheets("Sheet1").Cells.SpecialCells(xlCellType LastCell).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" wrote in message
...
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

Code for dragging the last column
 

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


"Gaurav" wrote in message
...
Sorry I did not change the sheet name. I have changed the sheet name and
now i am getting error 400.


"Ian" wrote in message
...
Sub test()
Worksheets("Sheet1").Unprotect password:="test"
myrange =
Worksheets("Sheet1").Cells.SpecialCells(xlCellType LastCell).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" wrote in message
...
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

Code for dragging the last column
 

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(xlCellType LastCell).Column
Cells(1, myrange).EntireColumn.Copy
Cells(1, myrange + 1).PasteSpecial (xlPasteFormulas)
Worksheets("Sheet1").Protect password:="test"
End Sub

Ian


"Gaurav" wrote in message
...
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


"Gaurav" wrote in message
...
Sorry I did not change the sheet name. I have changed the sheet name and
now i am getting error 400.


"Ian" wrote in message
...
Sub test()
Worksheets("Sheet1").Unprotect password:="test"
myrange =
Worksheets("Sheet1").Cells.SpecialCells(xlCellType LastCell).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" wrote in message
...
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