PDA

View Full Version : Rearrange columns



pawasthi
04-20-2011, 05:32 PM
Hi all,

My problem statement is as below:

I have a worksheet a.xls. I have 40 columns & variable no of rows. The requirement is to filter out certain rows (defined criteria) and then in a separate worksheet say B.xls, copy the few columns of the identified rows.

ex Copy column A, C, E, F, G, I.... and also these columns need to pasted in B.xls in a different order... Say new column, new column, A, E, I, new column, C....


New Column - It stands for blank cell

Currently I am able to copy the whole row but not able to copy the sub set of columns and then rearrange them on B.xls.

Thanks in advance..


If (cValue1 = "XYZ" And cValue2 = "Source" And StrComp(cValue3, MyInput, vbTextCompare) = 0 And cValue4 = "Destination") Then

myrow = i
Rows(myrow).Select
Selection.Copy

'Set JndRng = Application.Union(Range("AK" & myrow & ":AK" & myrow), Range("H" & myrow & ":H" & myrow), Range("BA" & myrow & ":BA" & myrow))
' JndRng.Copy

ActiveSheet.Paste Destination:=Worksheets("sheet1").Cells(j, 1)
Application.CutCopyMode = False

Rows(myrow + 1).Select
Selection.Copy

'Set JndRng1 = Application.Union(Range("H" & myrow + 1 & ":H" & myrow + 1), Range("G" & myrow + 1 & ":G" & myrow + 1), Range("F" & myrow + 1 & ":F" & myrow + 1), Range("I" & myrow + 1 & ":I" & myrow + 1), Range("Y" & myrow + 1 & ":Y" & myrow + 1), Range("W" & myrow + 1 & ":W" & myrow + 1), Range("X" & myrow + 1 & ":X" & myrow + 1), Range("Q" & myrow + 1 & ":Q" & myrow + 1), Range("M" & myrow + 1 & ":M" & myrow + 1), Range("AJ" & myrow + 1 & ":AJ" & myrow + 1), Range("S" & myrow + 1 & ":S" & myrow + 1), Range("D" & myrow + 1 & ":D" & myrow + 1), Range("A" & myrow + 1 & ":A" & myrow + 1))
'JndRng.Copy


'ActiveSheet.Paste Destination:=Worksheets("sheet1").Cells(j + 1, 1)
'Application.CutCopyMode = False

j = j + 2

pawasthi
04-25-2011, 11:13 AM
Help Needed!!!