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

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

W.R.

Reply | Quote