Inserting rows and adding to total worksheet

S

SU123

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
 
D

Dave Peterson

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.
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
Click to expand...
 
S

SU123

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 said:
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.
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
Click to expand...
Click to expand...
 
D

Dave Peterson

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.
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 said:
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.
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Macro 7
Runtime Error '91' Object variable or With block variable not set 2
conso macro 0
Find copy data from a cell in Personal workbook and copy it to a cellin current workbook 14
How to say do nothing 2
NEXT 11
Select, find in another book, copy, return and paste 3
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1

Top