Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to update hyperlinks through multiple sheets but can't | Excel Worksheet Functions | |||
How to update data from multiple sheets to one specific sheets | Excel Discussion (Misc queries) | |||
Update same charts in multiple sheets at once | Excel Worksheet Functions | |||
Update multiple sheets | Excel Worksheet Functions | |||
Update multiple sheets at once | Excel Discussion (Misc queries) |