-
Solved: vlookup and copy data to spreadsheet
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
-
Can you post the code that you have created together with sample workbooks? We are here to assist, not to do everything from scratch.
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'
-
Sorry Md you are right here is the file.
Hope this helps it's a lot of data in one.
-
I still don't follow this. Can you post both files with sample data/layouts? What code have you written so far?
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'
-
I've deleted the attachments. Please don't post files containing real email addresses etc. Document Properties also contain details about your company.
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'
-
Thanks Md I appreciate it.
I have attached new spreadsheet showing my goal.
Hope that helps.
-
-
-
I need some help with this script.
[VBA]
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
[/VBA]
This is a part of what I am trying to do.
-
Ok I figured it out.
[vba]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[/vba]
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.
-
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'
-
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.
-
[VBA]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
[/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'
-
That works perfect Md. I appreciate it.
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