-
How use same code in other excel workbooks
Hi there,
I have written below code but I'm able to run other workbooks since I have given perticular excle name and each time need change file in the code. Could you please suggest how set up without changing file in code?
[VBA]Private Sub AdvanceFilter()
Dim MyWorkbook As Workbook
Application.ScreenUpdating = False
Set MyWorkbook = Workbooks.Open(Filename:="C:\Users\pattanam\Desktop\R and D\Reclass LTST Details.xlsx", ReadOnly:=True)
Range("A6:Q54406").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("A3:Q4"), CopyToRange:=Range("W6"), Unique:=False
Columns("W:AG").Select
Range("W6").Activate
Selection.Copy
Workbooks("405 Jun 11-1.xlsm").Activate
Sheets("2401020").Select
Columns("Q:Q").Select
Range("Q8").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
MyWorkbook.Close False
Set MyWorkbook = Nothing
Range("AH7").Select
ActiveCell.FormulaR1C1 = "=RC[-8]*-1"
Range("AI7").Select
Selection.FillRight
Range("Z6:AA6").Select
Selection.Copy
Range("AH6").Select
ActiveSheet.Paste
Range("AH7:AI58").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.FillDown
ActiveWindow.SmallScroll Down:=-2
ActiveWindow.ScrollRow = 1
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AF7").Select
Application.CutCopyMode = False
ActiveWindow.LargeScroll Down:=1
Range("H94").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6]:R[89]C[-6],C[10]:C[26],17,0)"
Range("H94").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=78
Application.CutCopyMode = False
Range("Q:AI").Select
Selection.ClearContents[/VBA]
-
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