Wrox Programmer Forums
|
BOOK: Excel VBA 24-Hour Trainer 2nd edition
This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 14th, 2015, 04:01 PM
Registered User
 
Join Date: May 2015
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default VBA Code for adding rows to a table

I am very new to VBA and am having a hard time. It seems like the code I need to write is so hard. I'm sure its just a matter of time before I understand everything but this is what I am trying to do:
I need a code that will add a row to the bottom of multiple tables on multiple sheets, in addition, I want the formulas to drop down from the cell above it so the tables can calculate.

It seems difficult to me, I'm hoping that is because I am new at this.

TIA for your help!

Last edited by ttansey77; May 14th, 2015 at 04:10 PM..
 
Old May 14th, 2015, 09:20 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Thank you for buying my book.

Your task sounds do-able, but more details must be known about your workbook design in order to offer a solution that will fit your needs. For example:

• What does "multiple tables on multiple sheets" mean?
- more than one table on many worksheets?
- one table on many worksheets?
- one or more tables on *every* worksheet?

• How is your workbook designed?
- are the tables set up the same way, or all different?
- does every table contain a formula?
- what is the purpose of all those tables -- why so many?
- are the tables really tables in the sense that you have named them or identified them uniquely some way? Maybe they are named ranges?

• Why do you need this to be done?
- is it triggered by some event that takes place, such as the passing of a new calendar day, or the upload of some data from an external database or web query?
- how do you want to apply this change: when the (maybe) upload or web query takes place, or at some point when an authorized person clicks a button to make it happen?

Not trying to throw a lot of questions back at you, but there are many ways to solve the problem, some ways better than others depending on your workbook's design and what you are working with. Try posting back with more details and we can take it from there.
 
Old May 15th, 2015, 11:28 AM
Registered User
 
Join Date: May 2015
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Here are the answers to your questions:
there are 6 sheets that have tables. The titles of the columns on each table are the same but there are formulas in them that are different for each sheet. i.e one sheet calculates the attendance, another sheet calcs the modifiers, the room and board and the daily rate. The last sheet pulls all of that information together to create an upload file that will convert to text and go to a different system.

Right now the company is using an Access database that they are having a hard time maintaining. The database creates a lot of manual input that we can resolve using the file I created in Excel.

The end user will need to have a macro that will create a new line at the end of each table on each sheet and with any luck pop up a form to complete to populate the data in columns A-D. The formulas will need to carry down on each table separately because they are all different.

Hope that helps you help me!!!
 
Old May 15th, 2015, 04:26 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Your question has two facets. One facet deals with programming of how to code for the 6 tables such that a row will be added and formulas from the prior last row or each respective table will be copied down. The code will be easier to write, and to understand when you look at it, if you can say what the names of those 6 worksheets are.

The other facet is what I call a development request, where you need a pop up form to complete the various columns of the (presumably) 6 tables. That requires someone you who'd want to contact and show the workbook to, so they can know your needs, and they'd be doing work that basically is contract work and probably charge your business for that service.

But if I can help you add a new row to each table such that the new row has formulas in its field column(s) (of course no other data because that involves input from someone who knows what's needed per the development facet), then post back with the worksheet names and maybe we can work out a solution for that programming ("how do I") facet of your question.
The Following User Says Thank You to Tom Urtis For This Useful Post:
ttansey77 (May 15th, 2015)
 
Old May 15th, 2015, 05:15 PM
Registered User
 
Join Date: May 2015
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default

The "button" for the form box is just a hopeful request, we can manage without it. The rest, the add a row and drop down the formulas from the above row are necessary to the function of the worksheets.
The names of the worksheets are as follows:
Summary
Attendance
Modifiers
RRB
MOD calc
Daily Rate Calc


Thank you so much for your help!!
 
Old May 16th, 2015, 03:51 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

If, as you said...

• Your worksheets are named exactly as you listed them,
And
• Each of those worksheet has data on it,
And
• There is at least one formula in each worksheet's last row of data,

...Then:

The below macro will do what you want, copying each formula in the last row of each worksheet you listed into the next available respective row.

I placed comments in the code so you can see how it is done. I tested this a few times and am sure it works without errors based on the information you provided.

Code:
Sub AddRowFormulas()

'Declare variables.
Dim mySheets As Variant, intArray As Integer, LastRow As Long
Dim FormulaCell As Range
'Define the array for worksheets of interest.
mySheets = Array("Summary", "Attendance", "Modifiers", "RRB", "MOD calc", "Daily Rate Calc")

'Turn off ScreenUpdating.
Application.ScreenUpdating = False

'Open a loop for each worksheet where you want the formulas to be copied.
For intArray = LBound(mySheets) To UBound(mySheets)
'Open a With structure for each worksheet of interest.
With Worksheets(mySheets(intArray))
'Identify the last row of data for each worksheet of interest.
LastRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Loop through each cell in the last row that contains a formula.
For Each FormulaCell In .Rows(LastRow).SpecialCells(xlCellTypeFormulas)
'Copy the last row's formula(s) to the next row.
FormulaCell.Copy .Cells(FormulaCell.Row + 1, FormulaCell.Column)
'Continue looping through the formula-containing cells in that last row.
Next FormulaCell

'Close the With structure for the worksheet being worked on.
End With
'Continue with the next worksheet of interest.
Next intArray

'Turn on ScreenUpdating.
Application.ScreenUpdating = True

'Inform the user that the task is completed.
MsgBox "New rows with formulas copied down.", 64, "Complete!"

End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
I Need Help Vba Code Automatically Sort Rows Largest To Smallest etheer Excel VBA 2 February 22nd, 2014 04:23 PM
keep adding table rows on button click wlin ASP.NET 2.0 Basics 0 September 26th, 2006 02:14 PM
page take time to load after adding rows in table avanishp General .NET 5 June 21st, 2005 02:32 PM
Adding rows to a table in ASP.NET codebehind GilletteCat ASP.NET 1.0 and 1.1 Professional 2 August 3rd, 2004 02:56 PM
adding VBA code to VB jamie123 VB How-To 1 April 27th, 2004 02:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.