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
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