Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting rows and adding to total worksheet
Hi
I have been looking at the various answers already suggested but havent quite got the answer I need. I have a workbook with a multiple sheet and a totals sheet. When i add a line to the individual sheet i need to also add it to the total sheet. I have created a macro to do this, but it is using absolute values instead of relative ones. How can i change this - a cut down version of the macro is enlosed - ---- finds where i need to insert the row and want to insert above this--- Cells.Find(What:="TOTAL F", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Rows("21:21").Select Selection.Insert Shift:=xlDown --- issue here is the absolute cell reference ---- Range("Q20").Select Selection.Copy Range("Q21").Select ActiveSheet.Paste Range("B21").Select Sheets("Totals").Select Application.CutCopyMode = False ActiveSheet.Unprotect ---- finds where i can add the line to the 'Totals' sheet---- Cells.Find(What:="TOTAL F", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate --- again goes to absolute cell ---- Range("A18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A19").Select Application.CutCopyMode = False Selection.EntireRow.Insert Range("C18:H18").Select Selection.AutoFill Destination:=Range("C18:H19"), Type:=xlFillDefault Range("C18:H19").Select Range("A19").Select ActiveCell.FormulaR1C1 = "='Aug 08 - Oct 08'!R[2]C[1]" Range("A19").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Aug 08 - Oct 08").Select End Sub Hope this is clear and thanx in advance SU |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting rows and adding to total worksheet
I find it pretty difficult to understand what's being selected and why.
You find a cell with "total f" in it, but then you select another cell. I'm guessing that you're selecting the cell above to work on it. But that could be completely incorrect. Anyway, this is my guess. Option Explicit Sub testme() Dim FoundCell As Range Dim wks As Worksheet Dim RngToTransfer As Range Set wks = ActiveSheet With wks Set FoundCell = .Cells.Find(What:="TOTAL F", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "Not found on this sheet" Exit Sub '???? End If 'insert above the "total F" cell? FoundCell.EntireRow.Insert .Cells(FoundCell.Row - 2, "Q").Copy _ Destination:=.Cells(FoundCell.Row - 1, "Q") 'isn't this cell empty, since the row has just been inserted??? Set RngToTransfer = .Cells(FoundCell.Row - 1, "B") End With With Worksheets("Totals") .Unprotect Set FoundCell = .Cells.Find(What:="TOTAL F", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "Not found on total sheet" Exit Sub '???? End If 'insert a row above the total f row FoundCell.EntireRow.Insert 'Column A of the 3 rows above the TOTAL F row 'includes the newly inserted row With .Cells(FoundCell.Row - 3, "A").Resize(2, 1) .Value = .Value End With 'fill the row above the inserted row onto the inserted row With .Cells(FoundCell.Row - 2, "C").Resize(1, 5) .AutoFill Destination:=.Resize(2, 5) End With 'change column A of the newly inserted row .Cells(FoundCell.Row - 1, "A").Formula _ = "=" & RngToTransfer.Address(external:=True) .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End With End Sub Maybe it'll get you closer. SU123 wrote: Hi I have been looking at the various answers already suggested but havent quite got the answer I need. I have a workbook with a multiple sheet and a totals sheet. When i add a line to the individual sheet i need to also add it to the total sheet. I have created a macro to do this, but it is using absolute values instead of relative ones. How can i change this - a cut down version of the macro is enlosed - ---- finds where i need to insert the row and want to insert above this--- Cells.Find(What:="TOTAL F", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Rows("21:21").Select Selection.Insert Shift:=xlDown --- issue here is the absolute cell reference ---- Range("Q20").Select Selection.Copy Range("Q21").Select ActiveSheet.Paste Range("B21").Select Sheets("Totals").Select Application.CutCopyMode = False ActiveSheet.Unprotect ---- finds where i can add the line to the 'Totals' sheet---- Cells.Find(What:="TOTAL F", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate --- again goes to absolute cell ---- Range("A18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A19").Select Application.CutCopyMode = False Selection.EntireRow.Insert Range("C18:H18").Select Selection.AutoFill Destination:=Range("C18:H19"), Type:=xlFillDefault Range("C18:H19").Select Range("A19").Select ActiveCell.FormulaR1C1 = "='Aug 08 - Oct 08'!R[2]C[1]" Range("A19").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Aug 08 - Oct 08").Select End Sub Hope this is clear and thanx in advance SU -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting rows and adding to total worksheet
hi Dave
thanx for trying to make sense of this. Yes the search for TOTAL F is to find where i want to insert a line (2 lines above this), and then copy the formulae on the line above that e.g. 1 A B ... E F 2 Company A 1 ... 3 (sum B1:E1) 3 Company B 3 ... 7 (sum B2:E2) 4 filler line 5 Total F sum(B1:B4) I want to add Company C - and want to ensure the totals are reflected in the total F. There will also be categories B/ M and K as well as F. This worksheet is used for Jan - Mar with different ones for Apr - Jun etc with a totals worksheet, where we may have Company A and Company D. Where the annual totals are calculated. So when i add company J between line 3 and 4, I need to also add it to the totals worksheet in its correct place i.e. above Total F for these subcategories. I hope this clears things up. The macro i created works once but as Total F moves down (either due to extra companies within its breakdown, or that of other totals above, the Macro will not do what it needs to. REgards SU "Dave Peterson" wrote: I find it pretty difficult to understand what's being selected and why. You find a cell with "total f" in it, but then you select another cell. I'm guessing that you're selecting the cell above to work on it. But that could be completely incorrect. Anyway, this is my guess. Option Explicit Sub testme() Dim FoundCell As Range Dim wks As Worksheet Dim RngToTransfer As Range Set wks = ActiveSheet With wks Set FoundCell = .Cells.Find(What:="TOTAL F", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "Not found on this sheet" Exit Sub '???? End If 'insert above the "total F" cell? FoundCell.EntireRow.Insert .Cells(FoundCell.Row - 2, "Q").Copy _ Destination:=.Cells(FoundCell.Row - 1, "Q") 'isn't this cell empty, since the row has just been inserted??? Set RngToTransfer = .Cells(FoundCell.Row - 1, "B") End With With Worksheets("Totals") .Unprotect Set FoundCell = .Cells.Find(What:="TOTAL F", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "Not found on total sheet" Exit Sub '???? End If 'insert a row above the total f row FoundCell.EntireRow.Insert 'Column A of the 3 rows above the TOTAL F row 'includes the newly inserted row With .Cells(FoundCell.Row - 3, "A").Resize(2, 1) .Value = .Value End With 'fill the row above the inserted row onto the inserted row With .Cells(FoundCell.Row - 2, "C").Resize(1, 5) .AutoFill Destination:=.Resize(2, 5) End With 'change column A of the newly inserted row .Cells(FoundCell.Row - 1, "A").Formula _ = "=" & RngToTransfer.Address(external:=True) .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End With End Sub Maybe it'll get you closer. SU123 wrote: Hi I have been looking at the various answers already suggested but havent quite got the answer I need. I have a workbook with a multiple sheet and a totals sheet. When i add a line to the individual sheet i need to also add it to the total sheet. I have created a macro to do this, but it is using absolute values instead of relative ones. How can i change this - a cut down version of the macro is enlosed - ---- finds where i need to insert the row and want to insert above this--- Cells.Find(What:="TOTAL F", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Rows("21:21").Select Selection.Insert Shift:=xlDown --- issue here is the absolute cell reference ---- Range("Q20").Select Selection.Copy Range("Q21").Select ActiveSheet.Paste Range("B21").Select Sheets("Totals").Select Application.CutCopyMode = False ActiveSheet.Unprotect ---- finds where i can add the line to the 'Totals' sheet---- Cells.Find(What:="TOTAL F", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate --- again goes to absolute cell ---- Range("A18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A19").Select Application.CutCopyMode = False Selection.EntireRow.Insert Range("C18:H18").Select Selection.AutoFill Destination:=Range("C18:H19"), Type:=xlFillDefault Range("C18:H19").Select Range("A19").Select ActiveCell.FormulaR1C1 = "='Aug 08 - Oct 08'!R[2]C[1]" Range("A19").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Aug 08 - Oct 08").Select End Sub Hope this is clear and thanx in advance SU -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting rows and adding to total worksheet
Save a copy of your workbook for testing.
The copy that suggested code into a module and step through it (place the cursor in the code and hit F8). You can see how each line of code changes your worksheet. (Just swap back to excel to see.) You may have to adjust the "FoundCell.Row +/- ###" to make sure the row that's changing is correct. But try it and see if you can modify the code. SU123 wrote: hi Dave thanx for trying to make sense of this. Yes the search for TOTAL F is to find where i want to insert a line (2 lines above this), and then copy the formulae on the line above that e.g. 1 A B ... E F 2 Company A 1 ... 3 (sum B1:E1) 3 Company B 3 ... 7 (sum B2:E2) 4 filler line 5 Total F sum(B1:B4) I want to add Company C - and want to ensure the totals are reflected in the total F. There will also be categories B/ M and K as well as F. This worksheet is used for Jan - Mar with different ones for Apr - Jun etc with a totals worksheet, where we may have Company A and Company D. Where the annual totals are calculated. So when i add company J between line 3 and 4, I need to also add it to the totals worksheet in its correct place i.e. above Total F for these subcategories. I hope this clears things up. The macro i created works once but as Total F moves down (either due to extra companies within its breakdown, or that of other totals above, the Macro will not do what it needs to. REgards SU "Dave Peterson" wrote: I find it pretty difficult to understand what's being selected and why. You find a cell with "total f" in it, but then you select another cell. I'm guessing that you're selecting the cell above to work on it. But that could be completely incorrect. Anyway, this is my guess. Option Explicit Sub testme() Dim FoundCell As Range Dim wks As Worksheet Dim RngToTransfer As Range Set wks = ActiveSheet With wks Set FoundCell = .Cells.Find(What:="TOTAL F", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "Not found on this sheet" Exit Sub '???? End If 'insert above the "total F" cell? FoundCell.EntireRow.Insert .Cells(FoundCell.Row - 2, "Q").Copy _ Destination:=.Cells(FoundCell.Row - 1, "Q") 'isn't this cell empty, since the row has just been inserted??? Set RngToTransfer = .Cells(FoundCell.Row - 1, "B") End With With Worksheets("Totals") .Unprotect Set FoundCell = .Cells.Find(What:="TOTAL F", _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "Not found on total sheet" Exit Sub '???? End If 'insert a row above the total f row FoundCell.EntireRow.Insert 'Column A of the 3 rows above the TOTAL F row 'includes the newly inserted row With .Cells(FoundCell.Row - 3, "A").Resize(2, 1) .Value = .Value End With 'fill the row above the inserted row onto the inserted row With .Cells(FoundCell.Row - 2, "C").Resize(1, 5) .AutoFill Destination:=.Resize(2, 5) End With 'change column A of the newly inserted row .Cells(FoundCell.Row - 1, "A").Formula _ = "=" & RngToTransfer.Address(external:=True) .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End With End Sub Maybe it'll get you closer. SU123 wrote: Hi I have been looking at the various answers already suggested but havent quite got the answer I need. I have a workbook with a multiple sheet and a totals sheet. When i add a line to the individual sheet i need to also add it to the total sheet. I have created a macro to do this, but it is using absolute values instead of relative ones. How can i change this - a cut down version of the macro is enlosed - ---- finds where i need to insert the row and want to insert above this--- Cells.Find(What:="TOTAL F", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Rows("21:21").Select Selection.Insert Shift:=xlDown --- issue here is the absolute cell reference ---- Range("Q20").Select Selection.Copy Range("Q21").Select ActiveSheet.Paste Range("B21").Select Sheets("Totals").Select Application.CutCopyMode = False ActiveSheet.Unprotect ---- finds where i can add the line to the 'Totals' sheet---- Cells.Find(What:="TOTAL F", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate --- again goes to absolute cell ---- Range("A18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A19").Select Application.CutCopyMode = False Selection.EntireRow.Insert Range("C18:H18").Select Selection.AutoFill Destination:=Range("C18:H19"), Type:=xlFillDefault Range("C18:H19").Select Range("A19").Select ActiveCell.FormulaR1C1 = "='Aug 08 - Oct 08'!R[2]C[1]" Range("A19").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("Aug 08 - Oct 08").Select End Sub Hope this is clear and thanx in advance SU -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Multiple Rows Throughout Worksheet | Excel Worksheet Functions | |||
Inserting rows in worksheet | Excel Discussion (Misc queries) | |||
Adding total from one worksheet to another | Excel Worksheet Functions | |||
INSERTING ROWS IN PROTECTED WORKSHEET | Excel Discussion (Misc queries) | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions |