abdulncr
11-16-2012, 12:17 AM
Hi,
I have below code to extract from one sheet to another, i wanted to add three things more points to the code, could any one please help me.
1) it is extracting from only one sheet i have 10 sheet extracted one by one sheet name suresh,george,mathew....etc
2) eg:- if more than one cell not equal to "Y" in H9:H28, more than one row extracting. i wanted to extract only one.
3)in the D7 of sheet extract i have MMM-yy, i wanted of extract if B9:B28 of each sheet equal to or less than D7 of sheet Extract.
4)if any row is hidden in the sheet it is not extracting, i wanted to extract that also without unhiding in the ooriginal sheet and sheet Extract
Sub copysheet()
Dim r As Range, sh As Worksheet
Set r = Worksheets("suresh").Range("H9:AC28")
Set sh = Worksheets("Extract")
For Each cell In r
If UCase(cell.Value) <> "Y" Then
rw = sh.Cells(sh.Rows.Count, "C").End(xlUp).Row + 1
cell.EntireRow.Copy
If rw < 18 Then rw = 18
sh.Cells(rw, 1).PasteSpecial xlValues
sh.Cells(rw, 1).PasteSpecial xlFormats
End If
Next
End Sub
Thanks
Abdul
I have below code to extract from one sheet to another, i wanted to add three things more points to the code, could any one please help me.
1) it is extracting from only one sheet i have 10 sheet extracted one by one sheet name suresh,george,mathew....etc
2) eg:- if more than one cell not equal to "Y" in H9:H28, more than one row extracting. i wanted to extract only one.
3)in the D7 of sheet extract i have MMM-yy, i wanted of extract if B9:B28 of each sheet equal to or less than D7 of sheet Extract.
4)if any row is hidden in the sheet it is not extracting, i wanted to extract that also without unhiding in the ooriginal sheet and sheet Extract
Sub copysheet()
Dim r As Range, sh As Worksheet
Set r = Worksheets("suresh").Range("H9:AC28")
Set sh = Worksheets("Extract")
For Each cell In r
If UCase(cell.Value) <> "Y" Then
rw = sh.Cells(sh.Rows.Count, "C").End(xlUp).Row + 1
cell.EntireRow.Copy
If rw < 18 Then rw = 18
sh.Cells(rw, 1).PasteSpecial xlValues
sh.Cells(rw, 1).PasteSpecial xlFormats
End If
Next
End Sub
Thanks
Abdul