Update worksheets
 

Hi,

The following code uses a data sheet with values in columns that are
deleted from sheets accordingly leaving behind updated sheets1,2&3.

column1---------- values to be deleted from -------- sheet1
column2---------- values to be deleted from -------- sheet2
column3---------- values to be deleted from -------- sheet3
-
-
columnX----------values to be deleted from--------sheetX

Requirement is that instead of updated sheet1,2&3, we should have
updated remaining data in new sheets i.e. sheet4,5&6 leaving sheet1,
sheet2 & sheet3 with original data.

sheet4------- update of ------------- sheet1
sheet5------- update of ------------- sheet2
sheet6------- update of ------------- sheet3


Summary sheet7 (summary of working)
------------------------------------------------------------------------------
columnA columnB
columnC columnD
Sheetname: value count columnC(sheet1,2,3) Removed
Unremoved
sheet1
100 20 5
sheet2
100 35 4
sheet3
100 10 1

sheet4 80
sheet5 65
sheet6 90
-----------------------------------------------------------------------------

The unremoved values to be listed in columnG,H & I of summary sheet.
columnG columnH columnI
1(value) 1 1
2(value) 2
3(value) 3
4(value) 4
5(value)


Also you can remove MsgBox ("Did not find number " & findNumber)
alert.

--------------------------
Code:
---------------------------
Sub removerows()

Const N = 3 'Number of sheets

With Sheets("Datasheet")
For SheetCount = 1 To N
SheetName = "Sheet" & SheetCount
Sheets(SheetName).Activate
Cells.Select
Lastrow = .Cells(Rows.Count, SheetCount). _
End(xlUp).Row
For RowCount = 1 To Lastrow
findNumber = _
.Cells(RowCount, SheetCount).Value
found = False
'patch becaue find doesn't find number
' in cell "A1" when using selection method
Do While Cells(1, 1).Value = findNumber
Cells(1, 1).EntireRow.Delete
found = True
Loop
Do While (True)
Set c = Selection. _
Find(what:=findNumber, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
found = True
Else
Exit Do
End If
Loop
If found = False Then
MsgBox ("Did not find number " & findNumber)
End If
Next RowCount
Next SheetCount
End With
End Sub
'-----------End of code---------------

Thanks

Update worksheets
 

On Feb 23, 12:28*am, Sinner wrote:
Hi,

The following code uses a data sheet with values in columns that are
deleted from sheets accordingly leaving behind updated sheets1,2&3.

column1---------- values to be deleted from -------- sheet1
column2---------- values to be deleted from -------- sheet2
column3---------- values to be deleted from -------- sheet3
-
-
columnX----------values to be deleted from--------sheetX

Requirement is that instead of updated sheet1,2&3, we should have
updated remaining data in new sheets i.e. sheet4,5&6 leaving sheet1,
sheet2 & sheet3 with original data.

sheet4------- update of ------------- sheet1
sheet5------- update of ------------- sheet2
sheet6------- update of ------------- sheet3

Summary sheet7 (summary of working)
---------------------------------------------------------------------------*---
columnA * * * * * * * * * *columnB
columnC * * * * columnD
Sheetname: * value count columnC(sheet1,2,3) * * * Removed
Unremoved
sheet1
100 * * * * * * * * * * * * * * * * * * * 20 * * * * * * * * * 5
sheet2
100 * * * * * * * * * * * * * * * * * * * 35 * * * * * * * * * 4
sheet3
100 * * * * * * * * * * * * * * * * * * * 10 * * * * * * * * * 1

sheet4 * * * * * * * * * * * * * *80
sheet5 * * * * * * * * * * * * * *65
sheet6 * * * * * * * * * * * * * *90
---------------------------------------------------------------------------*--

The unremoved values to be listed in columnG,H & I of summary sheet.
columnG * * * * * * * columnH * * * * * * * * *columnI
* *1(value) * * * * * * * * * * *1 * * * * * * * * * * * * * 1
* *2(value) * * * * * * * * * * *2
* *3(value) * * * * * * * * * * *3
* *4(value) * * * * * * * * * * *4
* *5(value)

Also you can remove MsgBox ("Did not find number " & findNumber)
alert.

--------------------------
Code:
---------------------------
Sub removerows()

Const N = 3 * 'Number of sheets

With Sheets("Datasheet")
* *For SheetCount = 1 To N
* * * SheetName = "Sheet" & SheetCount
* * * Sheets(SheetName).Activate
* * * Cells.Select
* * * Lastrow = .Cells(Rows.Count, SheetCount). _
* * * * *End(xlUp).Row
* * * For RowCount = 1 To Lastrow
* * * * *findNumber = _
* * * * * * .Cells(RowCount, SheetCount).Value
* * * * *found = False
* * * * *'patch becaue find doesn't find number
* * * * *' in cell "A1" when using selection method
* * * * *Do While Cells(1, 1).Value = findNumber
* * * * * * Cells(1, 1).EntireRow.Delete
* * * * * * found = True
* * * * *Loop
* * * * *Do While (True)
* * * * * * Set c = Selection. _
* * * * * * * *Find(what:=findNumber, LookIn:=xlValues)
* * * * * * If Not c Is Nothing Then
* * * * * * * *c.EntireRow.Delete
* * * * * * * *found = True
* * * * * * Else
* * * * * * * *Exit Do
* * * * * * End If
* * * * *Loop
* * * * *If found = False Then
* * * * * * MsgBox ("Did not find number " & findNumber)
* * * * *End If
* * * Next RowCount
* *Next SheetCount
End With
End Sub
'-----------End of code---------------

Thanks

Any update??? : )