Sub summary()
Dim newworkbook As Workbook
Dim filetoopen As Variant
Dim openbook As Workbook
Dim wb As Workbook
Application.ScreenUpdating = False
Dim dattym As String
Dim lrow As Long
Dim lcol As Long
Dim rownum As Long
Dim list As Variant
Dim stdat As Date
Dim endat As Date
dattym = Format(CStr(Now), "YYYY-mm-dd")
Set newworkbook = Workbooks.Add
With newworkbook
.Title = file name
.SaveAs Filename:="D:\localhome\username\Desktop\folder name\" & "Pjm Training Summary - " & dattym
.Close
End With
[QUOTE]
[B]filetoopen = Application.GetOpenFilename(Title:="browse for your file & import range", Filefilter:="Excel Files(*.xls*),*xls*")[/B]
[/QUOTE]
If filetoopen <> False Then
Set openbook = Application.Workbooks.Open(filetoopen)
End If
openbook.ActiveSheet.Range("A5").CurrentRegion.Copy
Workbooks.Open("D:\localhome\username\Desktop\folder name\" & "file name- " & dattym).Activate
'Range("A1").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, none
Range("A5").PasteSpecial xlPasteAllUsingSourceTheme, xlPasteSpecialOperationNone, none
Selection.Columns.AutoFit
ActiveWorkbook.Save
ActiveWorkbook.Close
Workbooks.Open Filename:=("D:\localhome\user name\Desktop\file name")
lcol = WorksheetFunction.CountA(Range("G3", Range("G3").End(xlDown)))
list = Range(Cells(3, 7), (Cells(lcol + 2, 7))).Value
list = Application.Transpose(list)
list = Join(list, ",")
list = Split(list, ",")
list = Split(Join(Application.Transpose(Range(Cells(3, 7), Cells(lcol + 2, 7)).Value), ","), ",")
Workbooks.Open Filename:=("D:\localhome\mariappana\Desktop\Pjm Training Summary")
stdat = ActiveWorkbook.ActiveSheet.Cells(3, 8).Value
endat = ActiveWorkbook.ActiveSheet.Cells(3, 9).Value
Workbooks.Open("D:\localhome\mariappana\Desktop\PJM Training Week March\" & "Pjm Training Summary - " & dattym).Activate
With Selection
.AutoFilter Field:=14, Criteria1:=list, Operator:=xlFilterValues
.AutoFilter Field:=17, Criteria1:="<=" & CDbl(endat), Operator:=xlAnd, Criteria2:=">=" & CDbl(stdat)
End With
Application.ScreenUpdating = False
Sheets.Add.Name = "Data"
Sheets(1).Range("A5").CurrentRegion
Selection.Copy Destination:=Sheets("Data").Range("A5")
Selection.Columns.AutoFit
'Worksheets("Data").Range("A2").Value = "title of report " & dattym
Selection.Copy Destination:=Sheets("Data").Range("A5")
Selection.Columns.AutoFit
End Sub