jigar1276
07-01-2008, 12:26 AM
Hi Experts,
I have two xls files. The "data.xls" contains the 2 columns (A & B) list with N numbers of rows. The "report.xls" is having blank format containing 2 columns and only 10 rows to acomodated top 10 data.
The certain cells in column "B" of "data.xls" is highlighted using color which needs to be ignored while preparing the report.
The macro should acsending short the "data.xls" and then copy the 10 rows in "report.xls" by avoiding rows for which cells in column "B" of "data.xls" is highlighted using color.
I have tried using the following code, but in case of one highlighted cell, the 9 rows only copied to report file. if i highlight 2 cells, only 8 rows copied to report file. I need 10 rows in the report.
Thanks...
Sub PI()
'
'
'
Dim srnofrm As Long, srnoto As Long
Dim repf As String, dataf As String
pastpos = 10
srnofrm = 1
srnoto = 10
Windows("Data.xls").Activate
dataf = "Sheet" & Sht
Sheets(dataf).Select
Columns("A : D").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For srno = srnofrm To srnoto
Windows("Data.xls").Activate
dataf = "Sheet" & Sht
Sheets(dataf).Select
If Range("B" & srno).Interior.ColorIndex <> 6 Then
Range("A" & srno & ":B" & srno).Select
Selection.Copy
Windows("TOP_PI.xls").Activate
repf = "Sheet " & Sht
Sheets(repf).Select
Range("B" & pastpos).Select
ActiveSheet.Paste
pastpos = pastpos + 1
Else
srnoto = srnoto + 1
End If
Next srno
End Sub
I have two xls files. The "data.xls" contains the 2 columns (A & B) list with N numbers of rows. The "report.xls" is having blank format containing 2 columns and only 10 rows to acomodated top 10 data.
The certain cells in column "B" of "data.xls" is highlighted using color which needs to be ignored while preparing the report.
The macro should acsending short the "data.xls" and then copy the 10 rows in "report.xls" by avoiding rows for which cells in column "B" of "data.xls" is highlighted using color.
I have tried using the following code, but in case of one highlighted cell, the 9 rows only copied to report file. if i highlight 2 cells, only 8 rows copied to report file. I need 10 rows in the report.
Thanks...
Sub PI()
'
'
'
Dim srnofrm As Long, srnoto As Long
Dim repf As String, dataf As String
pastpos = 10
srnofrm = 1
srnoto = 10
Windows("Data.xls").Activate
dataf = "Sheet" & Sht
Sheets(dataf).Select
Columns("A : D").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For srno = srnofrm To srnoto
Windows("Data.xls").Activate
dataf = "Sheet" & Sht
Sheets(dataf).Select
If Range("B" & srno).Interior.ColorIndex <> 6 Then
Range("A" & srno & ":B" & srno).Select
Selection.Copy
Windows("TOP_PI.xls").Activate
repf = "Sheet " & Sht
Sheets(repf).Select
Range("B" & pastpos).Select
ActiveSheet.Paste
pastpos = pastpos + 1
Else
srnoto = srnoto + 1
End If
Next srno
End Sub