Paddy1979
Well-known Member
- Joined
- Sep 23, 2005
- Messages
- 608
Hello All,
Please can you help me .
The below Vlookup searches for this filename 'INNS148_-_Service_Provider_Stocks_Detail.xls .However the filename may now vary crashing the macro.
Can i remove the filename from the code by replacing it with 'newactive' or something similar, i have tried but i couldnt get it to work?
Sub Max_Stock()
Dim strFileName As Variant
Dim jstFileName As Variant
Set OldActive = ActiveSheet
strFileName = Application.GetOpenFilename(Title:="Open INNS148 Report")
If strFileName = False Then
MsgBox "No File Selected"
Exit Sub
End If
openfile
Workbooks.OpenText Filename:=strFileName, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
Set newactive = ActiveSheet
OldActive.Activate 'Return to sheet you came from
'Insert formulas
With Range("D9:D2999")
.Value = "=VLOOKUP(RC[-3],'[INNS148_-_Service_Provider_Stocks_Detail.xls]SP Stock Items at Part Level'!R4C5:R5000C9,5,0)/13*1.2,0)))"
Cheers
Paddy
Please can you help me .
The below Vlookup searches for this filename 'INNS148_-_Service_Provider_Stocks_Detail.xls .However the filename may now vary crashing the macro.
Can i remove the filename from the code by replacing it with 'newactive' or something similar, i have tried but i couldnt get it to work?
Sub Max_Stock()
Dim strFileName As Variant
Dim jstFileName As Variant
Set OldActive = ActiveSheet
strFileName = Application.GetOpenFilename(Title:="Open INNS148 Report")
If strFileName = False Then
MsgBox "No File Selected"
Exit Sub
End If
openfile
Workbooks.OpenText Filename:=strFileName, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
Set newactive = ActiveSheet
OldActive.Activate 'Return to sheet you came from
'Insert formulas
With Range("D9:D2999")
.Value = "=VLOOKUP(RC[-3],'[INNS148_-_Service_Provider_Stocks_Detail.xls]SP Stock Items at Part Level'!R4C5:R5000C9,5,0)/13*1.2,0)))"
Cheers
Paddy