New blank row, but contains all previous rows formatted information and formulae.

N

NNWCLOUD

New Member
Joined
Aug 18, 2015
Messages
3
Hello!

I am fairly new to this level of excel and any constructive applicable help is greatly appreciated! I am trying to create a form, that once the row is completed, it will create a new blank row, but which contains all the formulas of the previous row.

My form, has date formatting, totals and percentages.

[TABLE="width: 1048"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RECEIPT/[/TD]
[TD]TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RETAILER/[/TD]
[TD]ATTACHED[/TD]
[TD] CLAIM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]DESCRIPTION[/TD]
[TD]JOB REF[/TD]
[TD]SUPPLIER[/TD]
[TD]Y/N[/TD]
[TD] AMOUNT[/TD]
[TD]VAT[/TD]
[TD] NET[/TD]
[/TR]
[TR]
[TD]Mon 10 August 2015[/TD]
[TD]Diesal[/TD]
[TD]Phillips[/TD]
[TD]Moto[/TD]
[TD]Y[/TD]
[TD]£45.00[/TD]
[TD]£7.49[/TD]
[TD]£37.51[/TD]
[/TR]
[TR]
[TD]Sat 15 August 2015[/TD]
[TD]Toll[/TD]
[TD]Heath[/TD]
[TD]Bridge[/TD]
[TD]Y[/TD]
[TD]£12.80[/TD]
[TD]£2.13[/TD]
[TD]£10.67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Total[/TD]
[TD] Total[/TD]
[TD] Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£57.80[/TD]
[TD]£9.62[/TD]
[TD]£48.18[/TD]
[/TR]
</tbody>[/TABLE]



I am struggling to configure a way that can process all of it at once, without overlapping the totals or effecting other cells the wrong way.

I have tried researching it and added and edited codes within the worksheet, but I run into errors and name problems.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = [TotalVal].Row - 1 Then
Application.EnableEvents = False
[TotalVal].EntireRow.Insert
Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rDelete As Range
If Not Intersect(Target, Range("TotalVal").EntireColumn) Is Nothing Then


Application.EnableEvents = False
For Each rCell In Intersect(Target, Range("TotalVal").EntireColumn).Cells


If rCell.Row = Range("TotalVal").Row - 1 Then
If Len(rCell.Value) > 0 Then
Range("TotalVal").EntireRow.Insert
Else
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
ElseIf Len(rCell.Value) = 0 Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If


Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
Application.EnableEvents = True


End If
End Sub


That is what I have so far. It does add a new row, but does not copy the previous rows formulae, percentages etc and does not automatically delete unwanted rows.

I ideally want my form to look like this:



[TABLE="width: 1048"]
<tbody>[TR]
[TD]RECEIPT/[/TD]
[TD]TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RETAILER/[/TD]
[TD]ATTACHED[/TD]
[TD] CLAIM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]DESCRIPTION[/TD]
[TD]JOB REF[/TD]
[TD]SUPPLIER[/TD]
[TD]Y/N[/TD]
[TD] AMOUNT[/TD]
[TD]VAT[/TD]
[TD] NET[/TD]
[/TR]
[TR]
[TD]Mon 10 August 2015[/TD]
[TD]Diesal[/TD]
[TD]Phillips[/TD]
[TD]Moto[/TD]
[TD]Y[/TD]
[TD]£45.00[/TD]
[TD]£7.49[/TD]
[TD]£37.51[/TD]
[/TR]
[TR]
[TD]Sat 15 August 2015[/TD]
[TD]Toll[/TD]
[TD]Heath[/TD]
[TD]Bridge[/TD]
[TD]Y[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
</tbody>[/TABLE]
Total Total Total
£45.00 £7.49 £37.51




[TABLE="width: 1048"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



And then when I fill out and complete another row of data, it automatically adds another blank row, with each column formatted, but the totals are unaffected by this and still only produce the totals.

I hope I've made enough sense, please fire questions if I haven't. I would be tremendously grateful for someone to explain to me what I am doing wrong and how I should be going about this.

HUGE THANKS!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
see if this will modification will work for you
Code: 
If Len(rCell.Value) > 0 Then
  Range("TotalVal").EntireRow.Insert
 rCell.EntireRow.Copy
 Range("A" & rCell.Row).EntireRow.Copy
 Range("A" & rCell.Row + 1).PasteSpecial xlPasteFormulasAndNumberFormats 
 Else
  If rDelete Is Nothing Then
   Set rDelete = rCell
  Else
   Set rDelete = Union(rDelete, rCell)
  End If
 End If

I assume the second macro is the one you are using, since it will not allow two worksheet_change macros in the same sheet code module.
 
Upvote 0
I did not realise you could only have one worksheet_change macros. So should I delete both and put yours in? And do i still need a named cell for your code? If so what and where? Im so grateful for you getting back to me and giving me a hand! Thank you.
 
Upvote 0
To clarify, you can only have one worksheet_change procedure in a single worksheet. But that procedure can do more than one thing if properly composed. What i posted is not a complete procedure, it is a mocification to a part of the second procdure which you posted. You would need to make the modification to your porcedure. A procedure (macro) begins with the title line of "Private Sub...", "Sub..." or "Function...". The Private indicates that the code is intended to dwell in a non-standard code module such as a worksheet, ThisWorkbook or a UserForm. The Sub and Function procedures are normally entered into one of the numbered code modules which begin with Module1.
 
Upvote 0
Yeah, I understand now. I can put it all together properly. I won't be able to work on it tonight, but I'll let you know, thurs or friday. Colossal thanks! Big help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA for adding formulas to specific Columns when a new row is added.
Replies
7
Views
451
dreid1011
dreid1011
Y
  • Question Question
Insert new row automatically if a row on another sheet is populated
Replies
0
Views
263
YichGa
Y
F
  • Question Question
insert & delete new rows automatically
Replies
0
Views
369
Felgrand89
F
cgeorge4
  • Question Question
MACROProblem-Add Summing Row & Formatting to All New Sheets
Replies
3
Views
936
jbeaucaire
jbeaucaire

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top