PDA

View Full Version : How to move column?moving is possible,if so will it affect formulas?



Prity
09-18-2014, 01:15 AM
Hi All,

I have to move the column Z values to column B, like wise the columns from B to Y should be moved to - Column C to Z.

i have lot of formulas written in each n every cell of columns, thinking what might be the solution for this. How to resolve it.
And i cant re write the code as this application is very vast... Also this sheet is being referred in other sheets, like its taking values, tables from other sheets too. It will be so messy if i re write the code again, aslo it may consume lot of time, its like designing once again, n i dont have so much of time to do it.


NOTE: in the screen shot i have hidden some of the columns, in order to take the screen shot.
Hope i can get some Idea.

Kind Regards
Prity

MINCUS1308
09-18-2014, 08:25 AM
Does a simple copy and paste not get the job done??
Perhaps i'm not understanding the question.

1) Select the contents to be moved.
2) right click
3) select 'copy'
4) select the new location for the cells
5) right click
6) look under 'paste options'

you will see a number of options for pasting the copied content.
choose the appropriate method.

mikerickson
09-19-2014, 03:54 PM
If you Cut and Paste (as opposed to Copy and Paste) all formulas referencing those cells will adjust to the new location.

Prity
09-22-2014, 06:58 AM
Hi mikerickson,

I carried out following steps:
1. I inserted a new column, then the column B got moved to C, and C moved to D.... it continued, i.e all the column got moved 1 column further.
2. Now i cut the contains of Column Z and pasted in B, yes as u said all the formula's are fine. But in the columns which got shifted are throwing the errors


Run-time error '1004':
method 'Intersect' of object'


This moving of column is big problem.

Actually i have to change the column name of B to Z. How to do it,? and this will solve the problem


Thanks in Advance
Prity

mikerickson
09-24-2014, 07:50 AM
Formulas will adjust to Insert/Delete/CutPaste. As will Named Ranges. VBA like Range("D:F") will not adjust.
I'm not sure what your are doing via VBA , but if you use Names and VBA references like Range("namedRange"), that is robust against I/D/CtP

Aussiebear
09-25-2014, 04:19 AM
As Mike has suggested, name your ranges by giving them a name rather than a cell range reference.

SamT
09-25-2014, 05:08 AM
I carried out following steps:
1. I inserted a new column [B], ...
2. Now i cut the contains of Column Z and pasted in B,...


Sub Test()
Columns(26).Cut
Columns(2).Insert
End Sub

Prity
09-29-2014, 05:40 AM
Hi Sam,

I cant cut the merged cells. If i try then i am getting the following error.

Run time error-1004
cannot change part of merged cell.



Columns(26).Cut
Columns(3).Insert

Columns(5).Cut
Columns(4).Insert

Columns(6).Cut
Columns(5).Insert

Columns(7).Cut
Columns(6).Insert

Columns(8).Cut
Columns(7).Insert

Columns(9).Cut
Columns(8).Insert

Columns(10).Cut
Columns(9).Insert

Columns(11).Cut
Columns(10).Insert

Columns(12).Cut
Columns(11).Insert

Columns(13).Cut
Columns(12).Insert

Columns(14).Cut
Columns(13).Insert

Columns(15).Cut
Columns(14).Insert
Columns(16).Cut
Columns(15).Insert
Columns(17).Cut
Columns(16).Insert
Columns(18).Cut
Columns(17).Insert
Columns(19).Cut
Columns(18).Insert
Columns(20).Cut
Columns(19).Insert

Columns(21).Cut
Columns(20).Insert

For i = 22 To 100
Columns(i).Cut
For j = 21 To 100
Columns(j).Insert
Next j

Next i


ActiveSheet.Protect Password:=paswrd


For all the cells i am getting this error.

Regards
Prity



Sub Test()
Columns(26).Cut
Columns(2).Insert
End Sub

Prity
10-07-2014, 06:02 AM
Hi All,


1. I Inserted the new column B., then the existing column B moved to C. And the column C moved to D....

2. I cut the contents of column Z, n pasted it in the column B. The values of column Z are working fine. But the column B which got moved to C, is not working fine. its shrowing up errors.

I dont know whats the solution.

Help me out

Prity

SamT
10-07-2014, 08:02 AM
Try the sub "test" on a worksheet with no merged cells.

Prity
10-08-2014, 08:32 AM
Hi Sam

What is sub "test", with no merged cells.:think: How it will solve the cells which got shifted.

Thanks in advance
Prity


Try the sub "test" on a worksheet with no merged cells.

Aussiebear
10-08-2014, 01:39 PM
Its the section of code Sam provided in #7

SamT
10-08-2014, 04:11 PM
What Aussiebear said.. It will cut Column 26 (Z) and insert it before column2 (B). That insertion will shift all the other columns (B to Y) one place to the right.

Prity
10-10-2014, 12:39 AM
Hi Sam,

In my application i have sheet9, when ever i open this sheet(i mean as soon as this sheet9 gets loaded) i want the following code to get executed.


Columns(26).Cut
Columns(2).Insert


NOTE: i cant add any button control or any thing new to the design. So i am thinking where to put this above code.

Let me know where i can place that code.

Thanks in Advance
Kind Regards
Prity

Aussiebear
10-10-2014, 01:36 AM
Place the code provided by Sam in a module in the Sheet9

Sub Test()
Columns(26).Cut
Columns(2).Insert
End Sub

Prity
10-10-2014, 02:59 AM
Hi Aussiebear,

Do i need to add a new module by name - "module9" in the project. If i add how that code will get executed when i click on the sheet9?

Well there are so many sheets in this project. to add this piece of code its troubling me.

Well i wrote the code as below:



Private Sub Worksheet_Activate()
'Worksheets("Services").Select
ActiveSheet.Unprotect Password:="GORDO"
Columns(26).Cut
Columns(2).insert '-------------at this line i got the following error
ActiveSheet.Protect Password:="GORDO"
End Sub





i got the following error:
run-time error '1004'


I just found the error is getting caused at the following place, well i dont know why the courser is going to this piece of code.



Function GetPattern(Source As String, ByVal Pattern As String) As String
Dim X As Long, FindPattern As Long
Do Until Left(Pattern, 1) <> "*"
Pattern = Mid(Pattern, 2)
Loop
For X = 1 To Len(Source)
If Mid(Source, X) Like Pattern & "*" Then
FindPattern = X
Exit For
End If
Next
If FindPattern = 0 Then Exit Function
For X = 1 To Len(Source) - FindPattern + 1
If Mid(Source, FindPattern, X) Like Pattern Then
GetPattern = Mid(Source, FindPattern, X)
Exit For
End If
Next
End Function


NOTE: my project is passowrd protected. also it goes to so many other functions too in the code.
:-(

Thanks in Advance
Prity