Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why the column code in my excel is 123... instead of ABC...? | Excel Discussion (Misc queries) | |||
Excell dragging formula to change sheet/tab not column/row | Excel Worksheet Functions | |||
"Absolute" questions when autofilling-dragging down the column not | Excel Worksheet Functions | |||
Need keystrokes equivalent to dragging one column heading onto ano | Excel Discussion (Misc queries) | |||
dragging a formula down a column | Excel Worksheet Functions |