Although im an experienced user of excel VBA for certain purposes (functions) e.g. monte carlo
simulation, numerical integration etc. I am new to object oriented programming so i urge your patience. I have a piece of
code i am writing which will ultimately become an excel add in.
The puropse of the code:
Is to (with an existing blank workbook open) open other reference workbooks , filter the data in those workbooks according to
a certain criteria, copy the results, paste into the existing workbook and finally to close the referenced workbook.
via cheating and recording a macro i have been trying to develop the following code (which will then be looped in order to
do the same proceedure for several input files and paste the results as different sheets in the existing workbook)
One version of this code worked fine when pasting into another sheet in a workbook, but im having dificulties selecting the
data in the reference workbook (the one i didnt have open in the first place!):
'dates for files
'Dim strDate0 As String
'Dim strDate1 As String
Dim WbFailed As Workbook
strDate0 = Format(Date - 3, "yyyy.mm.dd")
strDate1 = Format(Date, "yyyy.mm.dd")
Application.ScreenUpdating = False
Set wbReport1 = ActiveWorkbook
Workbooks.Open filepath & "2006.10.24 Failed.xls"
Set WbFailed = ActiveWorkbook
'filter the data
Selection.AutoFilter Field:=4, Criteria1:="=3ECL", Operator:=xlOr, _
Selection.AutoFilter Field:=9, Criteria1:="=USD", Operator:=xlOr, _
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
Application.ScreenUpdating = True
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
wbfailed opens yet im not selecting the data from there!
Any indication as to the problems with the paste
operation/ wether the autofilter mode in excel can be used in such a way / logical or syntax errors in the above is much
thanks for your time