I am using the below Macro [which is only the second part] provided by Gowflow which is absolutely brilliant but would like to make a change to it which will make it even more brilliant
The [first part of the] Macro takes data from my file and copies this info into another file [Sign-Off-Log.xlsx] on Sheet1. This part of the Macro then apends to the initial data.
However, my file has 3 stages so I have put the code in Sheets1, 2 & 3. The code in Sheet 1 is fine and it does exactly what I need. For sheets 2 I need the Macro to open [Sign-Off-Log.xlsx] in Sheet2 and for Sheet3 I need the code to open the file [Sign-Off-Log.xlsx] in Sheet2.
I hope I have explained that ok
Sub UpdateLog(WS As Worksheet)
Dim MaxRow As Long
Dim oApp As Object
Dim WB As Workbook
Dim WSLog As Worksheet
Dim sFileName As String
Dim cCell As Range
Set oApp = CreateObject("Excel.application")
sFileName = ActiveWorkbook.Path & "\Sign-Off-Log.xlsx"
Set WB = oApp.Workbooks.Open(sFileName)
Set WSLog = WB.ActiveSheet
Set cCell = WSLog.Range("B:B").Find(what:=WS.Range("C5"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
WSLog.Range("D" & cCell.Row) = WS.Range("P3")
WSLog.Range("E" & cCell.Row) = WS.Range("Q3")
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Set WB = Nothing
Set WSLog = Nothing
Set oApp = Nothing