Insert new row below data already entered

[chris19]

Hello,

I have already entered data into an expenses spreadsheet. I have put totals at the bottom. To enable me to keep adding more entries, I would like a button to click on. Which will create a new row below the data but above the totals. Column A is the dates. I have been trying to go from the bottom of that entry and insert a new row however all the macros i am trying do not work.

Spread sheet: Date To From Reason Mileage Fare




------------------------------Total 100 100

Thank you for your help in advance.

Chris

[AB33]

Please Login or Register  to view this content.

[chris19]

Hello,

Thank you for your reply. I have tried this with and without the button and unfortunately it didn't work.

Kind Regards
Chris

[AB33]

Chris,
it does work on my sample. Please attach your sample.

[chris19]

Hello,

I have copied and pasted in what you have provided. When i click on the button nothing still happens. I have gone in design mode and this is what i have;

Option Explicit

Sub InsertRows()
Dim LR As Long, i As Long

Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 2 Step -1
If InStr(1, Cells(i, "A"), "Total", 1) > 0 Then Rows(i).Insert Shift:=xlDown
Next i

Application.ScreenUpdating = True

End Sub

Private Sub CommandButton1_Click()

End Sub

Every time i take out;

Private Sub CommandButton1_Click()

End Sub

When I go back in. It reappears.

Kind Regards
Chris

[AB33]

Chris,
Please use code tags with your codes.
Forget about button for a moment. You first need to run the code and see if it works.
Please do the following.
While you are at excel, the cursor should be on the actual sheet
Press Alt+F11 will take you to the module window,
Or click developer then visual basics will also take to project window, where you should be able to see the module.
Now, copy the above code and paste it on the module and press F5 to run it. When you press F5 and do not get any error and then go back to excel to see if the code has inserted rows.
It would be easier if you could attach a sample so that I can also attach the sample with the code and result.

[chris19]

Hello,

I have followed your instructions. I have run the code. It doesn't error however i have gone back to the spreadsheet and no rows have been added. What do you mean by sample? Do you want the spreadsheet?

Kind Regards
Chris

[AB33]

Chris,
Yes, so that I can include the code with the solution and I will then send back to this forum as an attachment, so that you can see it for yourself if the code works , or not.
Remove any confidential information, then go to advance then attachment

[chris19]

Hello,

Sorry here it is.



Kind Regards
Chris

[AB33]

Chris,
Thank you for attachment!
Now, you have more than 4 sheets. The total on some of the sheets appear in column, B , in others in column D. Which sheet do you want to insert a row on? All sheets?

[chris19]

Hello,

I would like it on Activities, Travel and Rehabilitation Equipment sheets.

Thank You
Chris

[AB33]

Assuming it is the sheet activities, this code works. Do not worry, I will send you as an attachment once we have the solution.

Please Login or Register  to view this content.

[chris19]

Hello,

Thank you ever so much this was driving me crazy!

Chris

[AB33]

Please find attached. There is a button on sheet activities (Top row E1 area)

[chris19]

Hello,

Thank you ever so much! Can i use this code on this macro for my other sheets?

Kind Regards
Chris

[AB33]

Chris,
A bit tricky one. The attached code works on three specified sheets, but you can use this code with any sheet as long as you adjust the column, i.e. where the word "total "is . For e.g. in this code , the word "total" is assume to be in column B, in other case it may in different column.

Please Login or Register  to view this content.

[chris19]

Hello AB33,

Will my spreadsheet be open for any user to see?
If so would you please delete it.

Thank You
Chris

[AB33]

I can not delete, but can empty all sheets, so become blank and no data (Done). I can not do the same with your attachment. You are the only one who you could do it, or ask the moderators to remove all your postings.

[chris19]

Hello AB33,

I am ever so sorry. I just opened the expenses attachment and can see you have deleted all the information on the spreadsheet.

Once again thank you ever so much!

Kind Regards
Chris

[AB33]

Chris,
I can only amend my own code. I had posted my attachment in post#14. I have amended that attachment, although you can see an attachment, when you open it has four sheets with blank.