Copy the last specific not null row to the next row with formulas

[nefkho]

please help,
i need a macro to copy a specific columns (A to F) and the last not null row to the next row with formulas, its like copy a1 to a2 but a1 have the value of PO.01 and i need to increment it to PO.02. while the other columns are formulas and need to be copied. attached is my sample file

thanks,

[Kelshaer]

here you go:
It will work once you type a value in column "B".
I have also attached the workbook with the code in it.

Please Login or Register  to view this content.

[nefkho]

thanks, will test later.

[nefkho]

it works no w i need to call a procedure and inserted it before the END SUB, the procedure will copy from a hidden sheet as my template and name the sheet from the value of column A, and it doesnt work the renaming dont work. please check my code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Dim p As Byte
Dim r As Long
Dim num As Long
r = Target.Row
p = VBA.InStr(1, Cells(r - 1, 1), ".")
num = CLng(VBA.Mid(Cells(r - 1, 1), p + 1, 20)) + 1
Cells(r, 1) = VBA.Left(Cells(r - 1, 1), p) & VBA.Format(num, "00")
Range("C" & r - 1 & ":F" & r - 1).Copy Destination:=Range("C" & r & ":F" & r)
Call CreateTabs3
End Sub
------------
Public Sub CreateTabs3()

On Error Resume Next

Sheets("POTemp").Visible = True
Sheets("MasterSheet").Select
' Determine how many Names are on Data sheet
FinalRow = Sheets("MasterSheet").Cells(Rows.Count, "A").End(xlUp).Row
CountR = Cells(Cells(Rows.Count, "A").End(xlUp).Row + 1, "A").Select
' Loop through each Name on the data sheet

For x = FinalRow To FinalRow
LastSheet = Sheets.Count
Sheets("MasterSheet").Select
Name = Range("A" & x).Value

Sheets("POTemp").Copy After:=Sheets(LastSheet)
'rename the sheet and put name in Cell B2
Sheets(LastSheet + 1).Name = Name
Sheets(Name).Select
Range("I3").Value = "=MasterSheet!B" & x
Next x

On Error GoTo 0
Sheets("POTemp").Visible = False
End Sub

[Kelshaer]

I tried your last workbook and it seems to work perfectly and renaming as well