If I insert a row in my worksheet, I have to change the row manua

[Paul]

Hello I was wondering if there is a way around this problem.
I have the following code.

Public Sub daily2()

Dim rng As Range, rng2 As Range
Dim sh As Worksheet, sh1 As Worksheet
Dim wsheetname As String
Dim res As Variant
Dim DT As Date, s As String
Dim sname As String
Worksheets("planning").Select
Range("A1").Select
I = 10
For g = 1 To 4
For f = 1 To 5
s = Worksheets("planning").Cells(2, 10).Value

Range("d2:h75").Copy

Worksheets.Add After:=Worksheets(Worksheets.Count)
Set sh1 = ActiveSheet




sh1.Select
ActiveCell.Offset(0, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
ActiveWindow.DisplayZeros = False

Worksheets("planning").Select

Range(Cells(2, I), Cells(75, I + 1)).Select

Selection.Copy
sh1.Select


ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste Link:=True

sname = Range("f1").Value
sh1.Name = FORMAT(sname, "ddd") & g

L = L + 1
I = I + 2
Next f
I = I + 4
Next g
Range("1:1").Select
End Sub

if I add a row in worksheets("planning"), I would then have to manually
change

Range("d2:h75").Copy
Range(Cells(2, I), Cells(75, I + 1)).Select

I need to paste as a link

also If I add a row in worksheet("planning") I want the new sheet to
automatically add a row. Is this possible?

thanks in advance

Paul

 

[Don Guillett]

try using lastrow instead of 75. change column from "a" to yours
lastrow=cells(rows.count,"a").end(xlup).row