Yes there is a Sheet called
Register where the whole process begins.
I have gone through the process below, and have highlighted each tab referenced.
It starts with the
Register tab.
A user enters text within the Register (giant table of data), and on each new row in column B, it will execute the below automatically, column B consists of a drop-down which a user must select, hence the below is linked to that cell. I want the links to be automatically created when a user begins to start a new entry.
The Create_Links is whereby it automatically creates a folder upon the server in a specific location with the value in column A for that particular row.
Code:
Public Sub Create_Links()
Dim lastRow As Long
Dim cell As Range
'Create hyperlinks in column AW for each cell in column AW starting at AW8
With Worksheets("Register")
lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
For Each cell In .Range("A8:A" & lastRow)
.Hyperlinks.Add anchor:=.Cells(cell.Row, "AW"), Address:="", TextToDisplay:="Create " & cell.Value & " folder"
Next
End With
End Sub
When the user has gotten to the end of that row, there will be a cell with the text "Raise Follow-up Form" if it is required. (after the user has created the folder)
When confirmed it takes the row the user is on, on the
Register tab and pastes it to
IIR_data tab, which holds that 1 line of data which has been copied.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Target.Value = "Raise Follow-up Form" Then
If Not Intersect(Target, Range("AY7:AY99999")) Is Nothing Then
Dim lMaxRows As Long
If Not Intersect(Target, Columns("AY")) Is Nothing Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "BA")).Copy
With Sheets("IIR_data")
lMaxRows = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
End If
Call Followup_Confirm
End If
End If
End If
End Sub
Which also triggers Followup_Confirm, as the below:
Code:
Sub Followup_Confirm() Dim Msg As String, Ans As Variant
Msg = "Confirm you would like to raise a Follow-up form"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
' Copy date and paste in cell on row
ActiveCell.FormulaR1C1 = "Form Raised"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=R4C52"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' Select the worksheet that is to be saved
Sheets("IIR_temp").Select
ActiveSheet.Copy ' Copies active sheet to a new workbook
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Range("A1").Value & Range("B1").Value, 56
MsgBox ActiveWorkbook.FullName
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Select
End Sub
When selected YES to the "Confirm Follow-up" - the data upon the
IIR_data is linked to the
IIR_temp
This is a template form created from the
IIR_data, basically it references cells to complete form in specific locations for the user (IIR_temp is an incident form, taking information from the data on IIR_data (the copied row)).
I want the
IIR_temp (the incident form) to be saved to the server within the location on
IIR_temp cell
A1, with the file name for that document to be from
IIR_temp cell
B1.
From what I can tell, when I run the macro... because the Public Sub Create_Links (first code box) is Public it runs all the time? Not sure if this is the case, I'm still learning.
But, when the new workbook is created (which your code does do!) it doesn't complete the Range A1 or B1 for the file location or file name or save as, it just jumps back to the Public Create_Links and throws up an error.
However, as this is a new worksheet I'm not sure how it could still be linked, but is is because I have the Main Spreadsheet still open, I believe it still just wants to run the Public Create_Links?
I need to have the Main Spreadsheet open and not close, hence the IIR_temp file is created and saved in its desired location.
I hope this makes more sense to the process?
Thank you,
