Is it possible to have a spreadsheet that will automatically insert
columns and/or rows when updated with new info that is imported to a
new worksheet of the same workbook?

---

Request for Question Clarification by endo-ga on 27 Sep 2004 04:26 PDT

Hi,

If you post complete information about what you want to do exactly
(including sample files), I can write a VBA macro to achieve it.

Thanks.
endo

---

Clarification of Question by kraken-ga on 27 Sep 2004 06:47 PDT

I need for the report worksheet to be generated from the Bay Homes
Estimated Job Expense worksheet.  I can update the Job expense
worksheet from my accounting program and would like the Report
worksheet to update based on the new information.  The number of lines
may change on Job Expense worksheet depending on work completed.

The contract/Final column is manually entered and would need to be
preserved as updates are done.

Sample - http://www.bay-homes.com/excel/3singleton-092704.xls

Please let me know if you need more info and a guesstimate on cost.

Thank you.

---

Request for Question Clarification by endo-ga on 27 Sep 2004 09:20 PDT

Thank you for the clarification.

On the report worksheet, how are columns G, H and I generated?

I should be able to do it tomorrow, is that ok?

For cost, feel free to tip whatever you deem is suitable.

Thanks.
endo

---

Clarification of Question by kraken-ga on 27 Sep 2004 10:00 PDT

G, H, & I are generated with the formulas in the cell.  This is part
of what I was entering manually and would like have automated.

Hope this helps.

Hi,

I've created a macro that almost completely creates the report from
the main spreadsheet.

The only cells that aren't created automatically are H98 and I98
because it is not clear where these sums should be in columns H and I.
i.e. when these columns end. If you let me know, I can modify the
macro to fill these cells automatically.

As a result, G115 in the report is also not automatically populated.

Everything else is the same as your sample report, although column E
needs to be filled manually as you specified.

There are a few assumptions made by the macro:

A. All the items have a cost code ID (i.e. column C is not blank).
This is because the macro determines the last row by looking for the
last item in column C. That also means that there can't be anything
below the last row in column C on the 'Job Expense' worksheet (the
sample worksheet you've included has a space in C107 and C109,
pressing delete on these cells clears them).

B. The total cell (D107) is two rows below the last row in column C.

C. The total for column F (F105) is at the same height as the last row in column C.


To copy the macro to your workbook, please do the following.

1. Open your workbook.
2. Go to the Tools->Options->Security menu and set 'Macro Security' to 'Medium'.
3. Press Alt+F11, this should open the Microsoft Visual Basic Editor.
4. Right click in the top left pane 'Project - VBA Project' and choose
'Insert->Insert Module'.
5. Paste the macro code below (at the bottom of this post).
6. Go to File->Save and save the file.
7. You can now close the Visual Basic Editor.

To run the macro:

1. Open your workbook, click on 'Enable macros' when prompted.

2. Delete the old 'Report' worksheet or rename it to something else,
otherwise the new report worksheet won't be able to be renamed to
'Report'.

3. Make sure you are on your 'Job Expense' worksheet. Make sure that
there is nothing in column C below the last "real" row of data (your
sample job expense sheet has a space in C107 and C109, just press
'delete' on these cells).

4. Press Alt+F8. Choose 'CreateReport' from the list if it is not already selected.

5. Click on 'Run'.

6. The report worksheet should now have been created and be at the
front of the list of worksheets.


If you have any questions, problems or wish any modifications, please
let me know before rating my answer by clicking on the 'Request for
Clarification' button. If you can clear up the issue with the SUM on
column H, I can modify the macro.

Please find the code for the macro below.

Thanks.
endo

Search Strategy:

Excel macro tutorial
://www.google.com/search?hl=en&lr=&ie=UTF-8&q=excel+macro+tutorial


Macro code:

Sub createReport()
'
' createReport Macro
'

'
    Sheets("Bay Home Estimated Job Expense").Select
    Sheets("Bay Home Estimated Job Expense").Copy Before:=Sheets(1)
    Range("E1").EntireColumn.Insert
    Range("A:A").ColumnWidth = 10.14
    Range("B:B").ColumnWidth = 14
    Range("C:C").ColumnWidth = 18
    Range("D:D").ColumnWidth = 12.86
    Range("E:E").ColumnWidth = 12.86
    Range("F:F").ColumnWidth = 16.71
    Range("E1").Value = "Contract/Final"
    Range("E1").Font.Bold = True
    Range("G:G").Delete
    Range("G:G").ColumnWidth = 13.29
    Range("H:H").ColumnWidth = 9.29
    Range("I:I").ColumnWidth = 9.86
    Range("H1:H1").Value = "To Be Paid"
    Range("I1:I1").Value = "Gain/Loss"
    Range("H1:H1").Font.Bold = True
    Range("I1:I1").Font.Bold = True
    LastRow = Cells(65536, 3).End(xlUp).Row

For i = 2 To LastRow

Range("G" & i) = "=IF(E" & i & "=0,D" & i & "-F" & i & ",E" & i & "-F" & i & ")"
    
Range("H" & i) = "=IF(G" & i & "<0,0,G" & i & ")"

Range("I" & i) = "=IF(G" & i & ">=0,D" & i & "-(F" & i & "+G" & i & "),G" & i & ")"

Next

Range("G2:I" & LastRow).NumberFormat = "0.00_);[Red](0.00)"

Range("C" & LastRow + 9).Value = "Estimated Cost"
Range("D" & LastRow + 9).Value = "=D" & LastRow + 2
Range("C" & LastRow + 10).Value = "Change Orders"
Range("C" & LastRow + 11).Value = "Total Estimated Cost"
Range("D" & LastRow + 11).Value = "=SUM(D" & LastRow + 9 & ":D" &
LastRow + 10 & ")"
Range("F" & LastRow + 9).Value = "Actual Expenses"
Range("G" & LastRow + 9).Value = "=F" & LastRow
Range("F" & LastRow + 10).Value = "Projected To Be Paid"
Range("F" & LastRow + 11).Value = "Projected Final Cost"
Range("G" & LastRow + 11).Value = "=SUM(G" & LastRow + 9 & ":G" &
LastRow + 10 & ")"

Range("F" & LastRow).Value = "=SUM(F2:F" & LastRow - 1 & ")"

ActiveSheet.Name = "Report"

    
End Sub

---

Clarification of Answer by endo-ga on 28 Sep 2004 09:42 PDT

Hi,

There are two lines in the macro code that have been truncated.

The two lines that read:

LastRow 10 & ")"

should be on the same line as their previous line. They should be on
the same lines that are highlighted in red by the Visual Basic Editor.

Sorry for any confusion, if you require any help, please let me know.

Thanks.
endo

---

Request for Answer Clarification by kraken-ga on 28 Sep 2004 10:37 PDT

Thanks for the answer.

I apologize but it will be tomorrow afternoon before I have a chance
to mess with it.

At that time I will ask for any help I may need.  I will also rate the
answer and add a tip.

Thanks for your effort - It is most appreciated.

kraken

---

Request for Answer Clarification by kraken-ga on 29 Sep 2004 12:13 PDT

I followed the steps you outlined in the answer, but I get a "Compile
Error : Syntax Error" with the following line highlighted

Range("D" & LastRow + 11).Value = "=SUM(D" & LastRow + 9 & ":D" & LastRow 10 & ")"

It is in the VB editor as one long line - not truncated.

This line is also in red text as is the line that begins Range ("G" & ........

Thanks

---

Clarification of Answer by endo-ga on 30 Sep 2004 03:56 PDT

Hi,

You're missing a '+' sign.

The end of the line should read: 

LastRow + 10 & ")"

Please let me know if you have any more problems.

Thanks.
endo

---

Request for Answer Clarification by kraken-ga on 30 Sep 2004 05:28 PDT

That did the trick.

Columns H & I are just the sums of those columns - if we can add this
it would be great.

Also, if I manually enter values in Column E, is there any way to
maintain those when creating an updated report or will they need to be
entered manually each time?

I tried to do this by updating the report by inserting a new sheet of
"Job Expense" values and renaming the sheets, but when generating the
report I got an error because the output "Report" has the same name.

Thanks - this is very very close

---

Clarification of Answer by endo-ga on 30 Sep 2004 08:09 PDT

Hi,

I've changed it so the sum of columns H and I are on the same line as the total.

If you want to keep the values in column E from a previous report,
rename your old report to 'old report' or something similar. Create a
new report as previously, then copy column E from the old report to
the new report. You can then delete the old report.

Is that ok?

The updated code is below, if you need any help please let me know. 

N.B. the same lines as previously will probably be truncated, so
please watch out for that.

Thanks.
endo

Sub createReport()
'
' createReport Macro
'

'
    Sheets("Bay Home Estimated Job Expense").Select
    Sheets("Bay Home Estimated Job Expense").Copy Before:=Sheets(1)
    Range("E1").EntireColumn.Insert
    Range("A:A").ColumnWidth = 10.14
    Range("B:B").ColumnWidth = 14
    Range("C:C").ColumnWidth = 18
    Range("D:D").ColumnWidth = 12.86
    Range("E:E").ColumnWidth = 12.86
    Range("F:F").ColumnWidth = 16.71
    Range("E1").Value = "Contract/Final"
    Range("E1").Font.Bold = True
    Range("G:G").Delete
    Range("G:G").ColumnWidth = 13.29
    Range("H:H").ColumnWidth = 9.29
    Range("I:I").ColumnWidth = 9.86
    Range("H1:H1").Value = "To Be Paid"
    Range("I1:I1").Value = "Gain/Loss"
    Range("H1:H1").Font.Bold = True
    Range("I1:I1").Font.Bold = True
    LastRow = Cells(65536, 3).End(xlUp).Row

For i = 2 To LastRow

Range("G" & i) = "=IF(E" & i & "=0,D" & i & "-F" & i & ",E" & i & "-F" & i & ")"
    
Range("H" & i) = "=IF(G" & i & "<0,0,G" & i & ")"

Range("I" & i) = "=IF(G" & i & ">=0,D" & i & "-(F" & i & "+G" & i & "),G" & i & ")"

Next

Range("G2:I" & LastRow).NumberFormat = "0.00_);[Red](0.00)"

Range("C" & LastRow + 9).Value = "Estimated Cost"
Range("D" & LastRow + 9).Value = "=D" & LastRow + 2
Range("C" & LastRow + 10).Value = "Change Orders"
Range("C" & LastRow + 11).Value = "Total Estimated Cost"
Range("D" & LastRow + 11).Value = "=SUM(D" & LastRow + 9 & ":D" &
LastRow + 10 & ")"
Range("F" & LastRow + 9).Value = "Actual Expenses"
Range("G" & LastRow + 9).Value = "=F" & LastRow
Range("G" & LastRow + 10).Value = "=H" & LastRow + 2
Range("F" & LastRow + 10).Value = "Projected To Be Paid"
Range("F" & LastRow + 11).Value = "Projected Final Cost"
Range("G" & LastRow + 11).Value = "=SUM(G" & LastRow + 9 & ":G" &
LastRow + 10 & ")"
Range("F" & LastRow).Value = "=SUM(F2:F" & LastRow - 1 & ")"

Range("H" & LastRow + 2).Value = "=SUM(H2:H" & LastRow & ")"
Range("I" & LastRow + 2).Value = "=SUM(I2:I" & LastRow & ")"


ActiveSheet.Name = "Report"

    
End Sub

---

Clarification of Answer by endo-ga on 30 Sep 2004 08:11 PDT

Hi,

Just adding instructions on how to update the macro:

1. Open the workbook.
2. Press Alt+F11 to open the editor.
3. You should already see the old code opened. Select it all and
delete it, replace it with the new code (watching out for the
truncated lines).
4. Save and close the editor.

Thanks.
endo

---

Clarification of Answer by endo-ga on 01 Oct 2004 02:02 PDT

Thank you for the great rating and very generous tip!

If you need any more help with writing Excel macros, I would be more
than happy to help.

Thanks.
endo

Very good answer great help with implementing it.
Thanks!!

Sure. Would need a lot more information, though. You can use a VBA
macro. For instance, the one at the link finds and deletes duplicate
values. Yours would simply loop through all the worksheets in the
workbook, and compile one worksheet with all entries, yes?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=135

All the data would come from a second worksheet in the same workbook
and would need to have formulas applied and reformat itself to
accomplish this.  If you are interested in trying it I can send an
example and post a new question with a higher payment amount.

Thanks.

I'm not a researcher, but go ahead and post a file if you can. I may
still get an answer for you, or perhaps some other researcher will.