+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    petercoe
    Guest

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

    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


  2. #2
    Dave Peterson
    Guest

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

    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 wrote:
    >
    > 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

  3. #3
    petercoe
    Guest

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

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


  4. #4
    petercoe
    Guest

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

    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 Peterson wrote:
    > 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 wrote:
    > >
    > > 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



  5. #5
    petercoe
    Guest

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

    petercoe wrote:
    > 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 Peterson wrote:
    > > 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)
    > >
    > >




    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".


    > > petercoe wrote:
    > > >
    > > > 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



  6. #6
    Dave Peterson
    Guest

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

    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.

    petercoe wrote:
    >
    > petercoe wrote:
    > > 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 Peterson wrote:
    > > > 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)
    > > >
    > > >

    >
    > 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".
    >
    > > > petercoe wrote:
    > > > >
    > > > > 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1