PDA

View Full Version : How use same code in other excel workbooks



PATTANAM
09-28-2011, 10:43 AM
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?

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

Kenneth Hobs
09-28-2011, 07:03 PM
Please see: http://www.excelguru.ca/node/7