Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need Help with automatical copy, paste then sum (Exel 2000)

    I've been bashing my head trying to get excel to copy from sheet 1 to sheet 2 and them automatically sum at the end of the last cell.
    I'm trying to remove as much of the manual labor involed in moving information from worksheet to another. So I would like to find out if it is possible to do the following.
    I have a worksheet (sheet 1) in workbook 1 that contains item Name in colunm A, Quantity in colunm B, and Ext Cost in colunm C, all of this information is pasted from another workbook, workbook2 . I also have a worksheet that is titled Manifest in workbook 1, I would like to copy all of the information in colunm A and colunm B automatically from sheet1 of workbook 1 into the worksheet titled Manifest of workbook 1, then some how have it sum colunm C automatically which will always have different number of row.
    Is there any possible way using a macro or VBA to accomplish this process?

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help with automatical copy, paste then sum (Exel 2000)

    If I interpret your description properly - try this as an event procedure on the manifest sheet.
    It should accomplish the copy each time you enter the sheet.

    Private Sub Worksheet_Activate()
    ActiveSheet.Cells.ClearContents
    Worksheets("Sheet1").Range("A:B").Copy _
    Destination:=ActiveSheet.Range("A:B")
    i = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.Cells(i, 3).Formula = "=SUM(Sheet1!C:C)"
    End Sub

  3. #3
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Help with automatical copy, paste then sum (Exel 2000)

    Andrew, you were right on the money. Your script worked as it should. But I do have a question what if I wanted to start pasting the information over into cells A13:B13. currently it strat in A1:B1, I ould like to offset this by at least 15 cell down.

    If I'm asking for to much please let me know.

  4. #4
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help with automatical copy, paste then sum (Exel 2000)

    Try the following where j is the offset - (currently 14)

    Private Sub Worksheet_Activate()
    ActiveSheet.Range(Cells(14, 1), Cells(ActiveSheet.UsedRange.Rows.Count, 3)).ClearContents
    i = Worksheets("Sheet1").UsedRange.Rows.Count
    j = i + 14
    Worksheets("Sheet1").Range("A1:B" & Format(i)).Copy _
    Destination:=ActiveSheet.Range("A14:B" & Format(j))
    ActiveSheet.Cells(j, 3).Formula = "=SUM(Sheet1!C:C)"
    End Sub

    Note that the routine fully cleared the sheet prior to loading - this was a quick method.
    I assume you're trying to preserve the first fourteen rows and am therefore only clearing columns A:C from 14 down to the EXISTING last cell


    Andrew

  5. #5
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Help with automatical copy, paste then sum (Exel 2000)

    Andrew, you did it again, this worked perfectly.
    Thank you very much for your help.

    W.R.

Posting Permissions

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