Update multiple sheets at once
 

Hi,

I have a master work book with data in multiple sheets.
I have another sheet in same master workbook with values that I want
to remove from each sheet of master work book to update it. Entire row
of each sheet should delete in order to get updated.

*Values means figure like 8977109987276633316 and more in length.

Example:
Master workbook
Sheet1: 10 values
Sheet2: 20 values
Sheet3: 20 values
Sheet(n):

Datasheet (also in masterwork book containing values to be removed)
column1 with values for sheet1 to be removed: 4
column2 with values for sheet2 to be removed: 7
column3 with values for sheet3 to be removed: 9
column(n)

End result:
Sheet1: 6 values
Sheet2: 13 values
Sheet3: 11 values

If values not found, then there should be an error message like
"continue with next sheet or not".

Would be a great help.

Update multiple sheets at once
 

If your sheets have headers in the first row you could do it using ADO
and selecting from both sheets.

If your data isn't stored that way then you have a lot of line-by-line
VBA programming to do. Good luck.

Update multiple sheets at once
 

try this code. I had problems with the find method when selecting an entire
worksheet. It didn't find the data in the first cell. Had to add a fix for
an error in VBA. I find the more I try to use the Find method the more
problems I have with this method.

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


"Sinner" wrote:

Hi,

I have a master work book with data in multiple sheets.
I have another sheet in same master workbook with values that I want
to remove from each sheet of master work book to update it. Entire row
of each sheet should delete in order to get updated.

*Values means figure like 8977109987276633316 and more in length.

Example:
Master workbook
Sheet1: 10 values
Sheet2: 20 values
Sheet3: 20 values
Sheet(n):

Datasheet (also in masterwork book containing values to be removed)
column1 with values for sheet1 to be removed: 4
column2 with values for sheet2 to be removed: 7
column3 with values for sheet3 to be removed: 9
column(n)

End result:
Sheet1: 6 values
Sheet2: 13 values
Sheet3: 11 values

If values not found, then there should be an error message like
"continue with next sheet or not".

Would be a great help.

Update multiple sheets at once
 

On Sep 2, 9:00 am, "
wrote:
If your sheets have headers in the first row you could do it using ADO
and selecting from both sheets.

If your data isn't stored that way then you have a lot of line-by-line
VBA programming to do. Good luck.


Yes I can have headers in the first row.
Can u help?

Thx

Update multiple sheets at once
 

I will let u know, thank you very much Joel.
Really appreciate.


On Sep 2, 10:52 am, Joel wrote:
try this code. I had problems with the find method when selecting an entire
worksheet. It didn't find the data in the first cell. Had to add a fix for
an error in VBA. I find the more I try to use the Find method the more
problems I have with this method.

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



"Sinner" wrote:
Hi,

I have a master work book with data in multiple sheets.
I have another sheet in same master workbook with values that I want
to remove from each sheet of master work book to update it. Entire row
of each sheet should delete in order to get updated.

*Values means figure like 8977109987276633316 and more in length.

Example:
Master workbook
Sheet1: 10 values
Sheet2: 20 values
Sheet3: 20 values
Sheet(n):

Datasheet (also in masterwork book containing values to be removed)
column1 with values for sheet1 to be removed: 4
column2 with values for sheet2 to be removed: 7
column3 with values for sheet3 to be removed: 9
column(n)

End result:
Sheet1: 6 values
Sheet2: 13 values
Sheet3: 11 values

If values not found, then there should be an error message like
"continue with next sheet or not".

Would be a great help.- Hide quoted text -

- Show quoted text -