Copy Worksheet and update hyperlinks

I have a billing workbook that I'd like to set up so when a new billing comes in, I can copy the worksheet to a new sheet. I can do this easy enough, but I'd like to link the current billing information from prior sheet to the new sheet. On the example I've added I'd like to link H9 on the Sheet1 (2) to J9 on the Sheet1, so the billing information updates. How can I set this up so when I copy a new sheet, Sheet1 (3) that the new sheet will pull from Sheet1 (2) and not from Sheet1?

Thanks in advance

I've been able to get some code from another post and I am trying to figure out this, but I'm over my head.

Using this code, but I'm not sure what I need or don't need.

I'm not sure what the endrow is counting
I'm not sure how to copy cells I11:I126 from the previous worksheet and paste to the newest worksheet

I know this is a mess, but I'm trying and not doing well

thanks in advance for the help!


Sub NewEstimate()
Dim h, i, u As Long
Dim sc, 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 > cs1 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(1).Activate
Range("I11:I126").Copy

Sheets(Sheets.Count).Activate
Range("G11:G126").PasteSpecial


End Sub

So, I've been able to get the code to copy a worksheet and add columns, but it only does it once, then erases info. Can someone help me get this finished?

I'm trying to get it so each new worksheet sums up the last sheet to the new ones.

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


Thank you in advance!

Anybody? Please and Thank you

Can someone at least tell me if this is possible? ANYONE?

Im guessing this isn't possible based on the amount of views and lack of responses. Thank you,