Solved: vlookup and copy data to spreadsheet

[Emoncada]

I have a spreadsheet that I would like when clicking on a buttonsave to Open a spreadsheet called C:\Asset Tracking.

Then look at the original Spreadsheet cell K5 match with the tab.
So if K5 = NC8230 then in the "Asset Tracking" go to NC8230 tab.

Then Look at cell B6 and vlookup in the "NC8230" Tab under Range("B$5:B$500") and if there is a match update that row with the new data.

Else if not found input the new data in the next available cell in column B.

So if Cell B6 is found then in that same row make these changes

Spreadsheet 1........."Asset Tracking"
AA1.................=....Column C "what ever row it was found in"
V1...................=....Column D "what ever row it was found in"

Else If not found

Same as above just need to add
Spreadsheet 1........."Asset Tracking"
B6...................=....Column B "Next empty row"
AA1.................=....Column C "Next empty row"
V1...................=....Column D "Next empty row"

Then save and close spreadsheet.

I know this is a bit of a challenge.

Any help would be great. Thanks

[mdmackillop]

Can you post the code that you have created together with sample workbooks? We are here to assist, not to do everything from scratch.

[Emoncada]

Sorry Md you are right here is the file.

Hope this helps it's a lot of data in one.

[mdmackillop]

I still don't follow this. Can you post both files with sample data/layouts? What code have you written so far?

[mdmackillop]

I've deleted the attachments. Please don't post files containing real email addresses etc. Document Properties also contain details about your company.

[Emoncada]

Thanks Md I appreciate it.
I have attached new spreadsheet showing my goal.

Hope that helps.

[Emoncada]

The other one.

[Emoncada]

BUMP!

[Emoncada]

I need some help with this script.


Dim rp As String
Range("K5").Value = rp
Workbooks.Open Filename:= _
"C:\Asset Tracking.xlsx"
If rp = "8510p" Then Sheets("8510p").Select
If rp = "NC8430" Then Sheets("NC8430").Select

End Sub

This is a part of what I am trying to do.

[Emoncada]

Ok I figured it out.

Dim rp As String
rp = Range("K5")
Workbooks.Open Filename:= _
"C:\Asset Tracking.xlsx"

Select Case rp

Case "8510p"

Sheets("8510p").Select

Case "NC8430"

Sheets("NC8430").Select

Case "NC8230"

Sheets("NC8230").Select

Case "NC8000"

Sheets("NC8000").Select

End Select

Range("B5:B15").Select

End Sub

Now I need to do a search or vlookup to match cell from Equipment Tracking and lookup in Column B. If found I need to change cell in that row.

How can I do that.

[mdmackillop]

Add this to Equipment Tracking Sheet module and set your path to suit. The code is triggered when the value in B6 is changed.
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

[Emoncada]

Md Im going to include this in part of the original script so I would need for this to be run when a cmdButton is clicked not when B6 is changed. How can I make this work.

[mdmackillop]

Option Explicit
Sub DoStuff()
Dim wb As Workbook
Dim ws As Worksheet
Dim ThsSht As Worksheet
Dim tgt As Range
Dim Target As Range
Dim MyPath As String

MyPath = "C:\Emon\" '<=====Change to suit

Application.ScreenUpdating = False
Set ThsSht = ActiveSheet
Set Target = ThsSht.Range("B6")
Set wb = Workbooks.Open(MyPath & "Asset Tracking.xlsx")
Set ws = wb.Sheets(ThsSht.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) = ThsSht.Range("AA1").Value
tgt.Offset(, 2) = ThsSht.Range("V1").Value
wb.Close True
Set wb = Nothing
Application.ScreenUpdating = True
End Sub

[Emoncada]

That works perfect Md. I appreciate it.