Emoncada
12-09-2008, 07:35 AM
MdMack was able to provide me with the following code. See previous question (http://vbaexpress.com/forum/showthread.php?t=24024)
It works perfectly for what I need.
I just have another spreadsheet that can use this, but needs to be modified.
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:\Docu\" '<=====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
What I need is instead of looking at just B6 I need it to grab a range from "E10:E1500" and I don't need to do a find(Target.Value) one because it will run and take a very long time.
Instead of
Set ws = wb.Sheets(ThsSht.Range("K5").Value)
I need it to look at Range ("D10:1500") and match with sheet.
If no match skip line.
Now instead of
tgt.Offset(, 1) = ThsSht.Range("AA1").Value
*This can be the same for all in the range it will be "D6"
Now for
tgt.Offset(, 2) = ThsSht.Range("V1").Value
I will need it to look at each row and grab the value in "O"
Hope that explains my new goal.
Thanks
It works perfectly for what I need.
I just have another spreadsheet that can use this, but needs to be modified.
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:\Docu\" '<=====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
What I need is instead of looking at just B6 I need it to grab a range from "E10:E1500" and I don't need to do a find(Target.Value) one because it will run and take a very long time.
Instead of
Set ws = wb.Sheets(ThsSht.Range("K5").Value)
I need it to look at Range ("D10:1500") and match with sheet.
If no match skip line.
Now instead of
tgt.Offset(, 1) = ThsSht.Range("AA1").Value
*This can be the same for all in the range it will be "D6"
Now for
tgt.Offset(, 2) = ThsSht.Range("V1").Value
I will need it to look at each row and grab the value in "O"
Hope that explains my new goal.
Thanks