Copy Paste

[vmjan02]

I am trying to explain this in a best possible way.

I have this file name Daily Performance Tracker V1.xlsm and in sheet name "Dashboard" with cell value in c3
and have downloaded files in folder Daily Performance Downloaded Files (all the file ext is .xlsx),

Trying to create a script were it will open one by one all .xlsx the files from the folder Daily Performance Downloaded Files
1. it will open the file from folder Daily Performance Downloaded Files
2. Then will select the sheet "Event Scheduler"
2. then will do autofilter till the last row (starting from cell d2 to Z2 are headers )
3. Autofilter Criteria:- will be Daily Performance Tracker V1.xlsm sheets("Dashboatd").range("c3").value
its date.
4. Once the date is filter from .xlsx file
5. need to paste only visible cells from .xlsx file to last row of .xlsm file in sheet Actual DailyData
6. It should do this till all the files from folder Daily Performance Downloaded Files are completed.

This code will not do this, any suggestion.

 Sub copydata()
 
    Windows("Daily Performance Tracker V1.xlsm").Activate
    ActiveWorkbook.Sheets("Actual DailyData").Activate
    Sheets("DownloadFiles").Unprotect "Qtech456$!"
       
    Set wbsource = Workbooks.Open(Application.ActiveWorkbook.Path & "\" & "Daily Performance Downloaded Files" & "\" & Sheets("DownloadFiles").Range("j2").Value)
           
    Windows("abcd-Daily Performance Report.xlsx").Activate
    Sheets("Event Scheduler").Select
    With ActiveSheet
        .AutoFilter = True
        
    End With

End sub

 

[vmjan02]

Did it and this code worked all perfect, posting it, if incase any of the person needs it
thanks a ton.

Solved.....

Sub GetAndOpenFile()
   Dim fPath As String
   Dim fName As String
   Dim fName1 As String
   Dim WB As Workbook
   Dim WB1 As Workbook
   
   Dim ctr As String
   
Set WB1 = ActiveWorkbook    ' you can set the workbook name
Sheets("sheet1").Select 'you can change the sheet name
   
   Dim lastRow As Long
   lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Dim i As Long
    For i = 2 To lastRow
fName1 = Cells(i, "J").Value
   
   
   ctr = WB1.Sheets("sheet1").Range("c2").Value
   'ctr = Workbooks("abc.xlsm").Sheets("Dashboard").Range("c2").Value
   
        On Error Resume Next
        fPath = Application.ActiveWorkbook.Path & "\" & "Folder Name"  'you can change the folder name
        fName = fPath & "\" & fName1 & ".xlsx"
        Set WB = Workbooks.Open(fName)
        
      
        
  With WB
        .Sheets("Sheet1").Select
         Dim lastRow1 As Long
         lastRow1 = Cells(Rows.Count, "E").End(xlUp).Row
        
    With Worksheets("sheet1")
      With .Range("V3:V" & .Range("E" & .Rows.Count).End(xlUp).Row)
           .Formula = "=E3"
           .Value = .Value
           .NumberFormat = "[$-en-US]dd-mm-yyyy;@"
       End With
     End With
        
   With Sheets("sheet1").Range("V2")
        .AutoFilter Field:=1, Criteria1:=ctr
   End With
        
   Range("D2:O" & lastRow1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
   WB1.Activate
   Sheets("sheet4").Select
    Range("A4").Select
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    'xlPasteFormats
    Application.CutCopyMode = False
    Range("A4").Select

    WB.Activate
    AutoFilter = False
    
    Application.DisplayAlerts = False
     WB.Close
    Application.DisplayAlerts = True
  End With
  
  WB1.Activate
  Sheets("sheet1").Select
   Next i
   Application.DisplayAlerts = False
   WB1.Save
   Application.DisplayAlerts = True
End Sub