cell linked with another cell

[xfr79]

I'm trying to add a section to this script below. What I want it to do is add a cell data from the worksheet that was created.
The way the script works, it asks for a month digit, day digit, and year digits. It then makes a copy of sheet "BACKLOG" and renames the sheet with the data the user put in for the month, day and year digits. (1.1.09)
What I then want to do after that is done is to go to sheet "TABLE" and find the next available row with no data, and add a cell link to certain cells in each row column. I've tagged the script below to indicate the cells being linked.



Private Sub CommandButton1_Click()
Unload Me
Sheets("BACKLOG").Visible = True
Worksheets("BACKLOG").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = monthoutput & "." & dayoutput & "." & yearoutput
On Error Resume Next
On Error GoTo 0
Range("A1").Select

' **********************************************

Select next available row with no data.

Column A
(add a cell link to the sheet created. =monthoutput & "." & dayoutput & "." & yearoutput & "I1")
Column B
(add a cell link to the sheet created. =monthoutput & "." & dayoutput & "." & yearoutput & "J30")
Column C
(add a cell link to the sheet created. =monthoutput & "." & dayoutput & "." & yearoutput & "J14")
Column D
(add a cell link to the sheet created. =monthoutput & "." & dayoutput & "." & yearoutput & "I7")

' **********************************************

Sheets("BACKLOG").Select
ActiveWindow.SelectedSheets.Visible = False
Application.Run "dvtosort"
Application.Run "dvto"
End Sub

[Kenneth Hobs]

Crossposted: http://www.mrexcel.com/forum/showthread.php?t=361649

Sub AddSheetAndLinks()
Dim bRow As Long

'setup some data for test purposes
Dim monthoutput As String, dayoutput As String, yearoutput As String, output As String
monthoutput = "1"
dayoutput = "1"
yearoutput = "09"
output = Join(Array(monthoutput, dayoutput, yearoutput), ".")

'Copy sheet, Backlog
Sheets("BACKLOG").Visible = True
Worksheets("BACKLOG").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = output

'Get row number of first fully blank row
bRow = LastNBRow(ActiveSheet.UsedRange) + 1

'Add formulas
Range("A" & bRow).Formula = "=I1"
Range("B" & bRow).Formula = "=J30"
Range("C" & bRow).Formula = "=J14"
Range("D" & bRow).Formula = "=I7"

' **********************************************
Sheets("BACKLOG").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub

'=LastNBRow(A3:G10)
Function LastNBRow(rng As Range) As Long
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = rng.Find(What:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
LastNBRow = LastRow
End Function

[xfr79]

My apologies Mr.hobs, i haven't realized you replied. I usually wait for an email notification, but I never got one, so i figured no one answered. That's why I cross posted.

Your script does exactly what I wanted and then some!

Thank you very much!!

If i decided to do another cross post, i'll add a link to the post.
I usually resort to that if no one responds.

[Aussiebear]

xfr79, you first posted this at MrExcel.com at 6.24am, and the first reply (other than you own updates) was at 8.32 am. Given that members of either forum are busy with their own lives, it is important to remember that you need to be patient. Posting the same issue here at 7.36 am of the same day doesn't show respect to anyone participating in either forum.

If you feel the need to cross post an issue between forums, then always put the link in as well. This way we don't waste our time on an issue which may well be solved.