Excel, adding figures from one cell to a summary sheet or workbook

[petercoe]

We are using a template for rental invoicing. There is one cell that
adds a damage waiver premium of 2% or 10% of the rental cost. In one
cell I make the choice of 2 or 10 and the amount automatically appears
in the damage waiver cell. We save the invoices as R1001, R1002, R1003
and so on. I need to extract the damage waiver costs in two different
totals, one for the 2% charge and one for the 10% charge because we
have to pay different percentages as insurance premiums for these
charges at the end of each month. Is there a way to do this
automatically so I don't have to view each invoice and add manually?

Tia, Petercoe

 

[Dave Peterson]

Maybe...

If all the workbooks are in the same folder and you pick up the same cell from
the same worksheet each time, you could use something like:

Option Explicit
Sub testme01()

Dim myFileNames As Variant
Dim RptWks As Worksheet
Dim wkbk As Workbook
Dim fCtr As Long

myFileNames = Application.GetOpenFilename("Excel Files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub
End If

Set RptWks = Workbooks.Add(1).Worksheets(1) 'single sheet

For fCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Workbooks.Open(Filename:=myFileNames(fCtr))
RptWks.Cells(fCtr, "A").Value = myFileNames(fCtr)
RptWks.Cells(fCtr, "B").Value _
= wkbk.Worksheets("sheet1").Range("a1").Value
wkbk.Close savechanges:=False
Next fCtr
End Sub

When you're prompted for what file to open, click on the first and ctrl-click on
subsequent (or shiftclick to extend your selection of files).

And remember to change the sheet name to what you want and the address, too--on
this line:

= wkbk.Worksheets("sheet1").Range("a1").Value

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[petercoe]

Thanks Dave. I have to work on it. I have never used macros.
Thanks again

 

[petercoe]

Thank you, Dave.
I have to learn all of this. As soon as I know, I will let you know how
this worked out.
Pete

 

[petercoe]

Thank you, Dave.
I have to learn all of this. As soon as I know, I will let you know how
this worked out.
Pete

Dave, When I get to the above line in VBA I get an error message as
follows:

Compile Error: Expected:List separator or )

Also I an expecting that I should substitute some actual file names
some where in this code?

I do not know much about macros. I only wrote one small basic program
in my life and a number of .BAT files when I used Dos 3.3
The file I'm using for test purposes is on my desktop and it's called
"Invoice Test". The workbooks? are R1001, R1002, R1003, etc and one
book called "Template".

 

[Dave Peterson]

That line shouldn't cause an error. Maybe something was inserted by google for
formatting???

If you delete that line and retype it (carefully), does it work ok?

And you'll have to select the files you want (click on the first and ctrl-click
(or shift-click)) on subsequent files in that dialog.

Just like you can open more than one file when you do file|open in excel.