-
Add this to Equipment Tracking Sheet module and set your path to suit. The code is triggered when the value in B6 is changed.
[VBA]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wb As Workbook
Dim ws As Worksheet
Dim tgt As Range
Dim MyPath As String
MyPath = "C:\Emon\" '<=====Change to suit
Application.ScreenUpdating = False
Set wb = Workbooks.Open(MyPath & "Asset Tracking.xlsx")
If Target.Address(0, 0) = "B6" Then
Set ws = wb.Sheets(Range("K5").Value)
Set tgt = ws.Columns(2).Find(Target.Value)
If tgt Is Nothing Then
Set tgt = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1)
tgt = Target.Value
End If
tgt.Offset(, 1) = Range("AA1").Value
tgt.Offset(, 2) = Range("V1").Value
End If
wb.Close True
Set wb = Nothing
Application.ScreenUpdating = True
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules