Copy worksheet and update

[padadof2]

I have a billing estimate workbook that we use for our projects. I'd like to be able to use VBA to copy the old worksheet to a new worksheet and update the numbers. We have Columns H/I that are the Previous billing quantity/Amount that has to be copied from the previous worksheet. We have columns J/K that are this application totals, Columns L/M are completed to date numbers which always have to be updated and then finally we have a Balance to finish columns N/O. The two cells are Quantity/Amount for each of the Billings. What I would like to accomplish, is being able to hit a macro button and the newest sheet in the workbook is copied, renamed to Estimate (X), and all the info from columns J/K are copied and pasted into H/I. With each new worksheet, I'd like to have columns L/M & N/O updated with each new sheet. I've attached a sample. I'm struggling how to keep a running total on the completed to date columns (L/M) and the Balance to Finish Columns (N/O)

Here is the code that I've pulled that allows me to copy a new worksheet and update the links, but it only works once, and then it zeros columns out.

Sub NewEstimate()

Dim h As Long, i As Long, u As Long
Dim sc As Long, sc1 As Long, sc2 As Long
Dim endrow As Long

endrow = Sheets(1).Cells(Rows.Count, 1).End(3).Row

h = 15
sc = Sheets.Count
h = h + 15

Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).DisplayRightToLeft = False
sc2 = Sheets.Count

If sc2 > sc1 Then
    u = h - 13
    Sheets(Sheets.Count).Name = "ESTIMATE" + Str(sc2 - 1)
End If

For i = 2 To Sheets.Count
    Sheets(i).Range("z2") = i - 1
    Sheets(i).Range("ab2") = Sheets.Count - 1
Next

Sheets(Sheets.Count - 1).Range("I11:I126").Copy
Sheets(Sheets.Count).Range("G11:G126").PasteSpecial xlPasteAll

Sheets(Sheets.Count).Range("I11:I126").ClearContents


End Sub

Here is a sample of the billing.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[padadof2]

Is this even possible? McFly?

 

[padadof2]

I'm guessing this isn't possible with the amount of views and lack of responses. Thank you

 

[CsJHUN]

dont give up, i cannot download at work, will do a workaround and check it tomorrow

 

[padadof2]

Thank you! I was giving up thinking this wasn't something able to be done. I appreciate it.

 

[CsJHUN]

Hi padaof2,

the file hasn't any macro (not that i would enable it ).
I write my own, and maybe simplified too much, also adjusted the "bill" formula:

sample billing(2) (version 2).xlsb
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
6	CONTRACT INFORMATION						BILLING INFORMATION
7	BID					TOTAL	Previous		This Application		Completed to Date		Balance to Finish
8	ITEM	DESCRIPTION	QUAN	UNIT	COST	AMOUNT	Quantity	Amount	Quantity	Amount	Quantity	Amount	Quantity	Amount
9	19	axel	10	EA	$ 5 000,00	$ 50 000,00	0,00	$ -	3,00	$ 15 000,00	3,00	$ 15 000,00	7,00	$ 35 000,00
10	20	Wheels	5	EA	$ 1 000,00	$ 5 000,00	0,00	$ -	1,00	$ 1 000,00	1,00	$ 1 000,00	4,00	$ 4 000,00
bill1

Cell Formulas
Range	Formula
K9:K10	K9	=F9*J9
L9:L10	L9	=J9
M9:M10	M9	=L9*F9
N9:N10	N9	=D9-L9
O9:O10	O9	=N9*F9
G9:G10	G9	=D9*F9
I9:I10	I9	=H9*F9

Make a box/button to first sheet (or if you have a form) and assign this code to it

Sub padadof2_new_estamination()
Application.ScreenUpdating = False
'https://www.mrexcel.com/board/threads/copy-worksheet-and-update.1122531/#post-5424617

asn = ActiveSheet.Name
Sheets(asn).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = "Estamination " & Sheets.Count
Range("J9:J126").Copy
Range("H9:H126").PasteSpecial xlPasteValues
Range("L9:L126").PasteSpecial xlPasteValues, Operation:=xlAdd
'Range("L9:L126").Value = Range("L9:L126").Value 'optional
Range("J9:J126").ClearContents
Application.ScreenUpdating = True
End Sub

I hope that helps.
The "optional" line maybe needed. The adding values to "completed" column will show how many times and what values added to it (for each estamination)
(btw the 3rd sheet in your wb is miscalculate the completed items, because adding the previous deliver 2 times)