Open Files

[Peter Quill]

Private Sub TarikDataKoreksi()
Dim lr, lrow, i As Long

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Sheet_Name = "Form Faktur"

'to select folder contains workbooks
Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select Folder"
If File_Dialog.Show <> -1 Then
Exit Sub
End If

File_Path = File_Dialog.SelectedItems(1) & "\"
File_Name = Dir(File_Path & "*.xls*")

'to open all workbook in folder and copy the data inside to sheet Monitoring Faktur
Do While File_Name <> ""
lr = Workbooks("Koreksi Faktur.xlsm").Worksheets("Monitoring Faktur").Range("A" & Rows.Count).End(xlUp).Row
lr = lr + 1
Set file = Workbooks.Open(FileName:=File_Path & File_Name)

'Nomor Faktur TanggalFaktur StatusSTNK JenisKoreksi DataAwal DataPerbaikan
file.Worksheets(Sheet_Name).Range("A4:G1000").Copy
Workbooks("Koreksi Faktur.xlsm").Worksheets("Monitoring Faktur").Range("A" & lr).PasteSpecial Paste:=xlPasteValues

'price
file.Worksheets(Sheet_Name).Range("U4:U1000").Copy
Workbooks("Koreksi Faktur.xlsm").Worksheets("Monitoring Faktur").Range("H" & lr).PasteSpecial Paste:=xlPasteValues

'no surat
file.Worksheets(Sheet_Name).Range("J1").Copy
Workbooks("Koreksi Faktur.xlsm").Worksheets("Monitoring Faktur").Range("J" & lr).PasteSpecial Paste:=xlPasteValues

'tgl pengajuan
file.Worksheets(Sheet_Name).Range("J2").Copy
Workbooks("Koreksi Faktur.xlsm").Worksheets("Monitoring Faktur").Range("I" & lr).PasteSpecial Paste:=xlPasteValues

File_Name = Dir()
Loop
end sub

 

[CJ_London]

please use code tags to around your code to preserve indentation.

The method I use is to use autofilter to filter the rows then take whatever action is required - use a macro builder to get what you want but as an example it might look something like this (copied from one of my apps)

with ws
     .UsedRange.AutoFilter Field:=5, Criteria1:="=Closing Balance"
     Set r = .Range(.cells(2, 1), .cells(.UsedRange.Rows.Count, i))
end with

ws is the worksheet object
UsedRange is the range occupied with data (I.e. A1 to bottom right)
field is the column number (need to be 1 in your case for column A).
criteria speaks for itself - in my case I want to find all the closing balance rows to format the row with font weights, lines etc. In your case it would be something like "=blank" - but you will need to check

Using your macro builder find the code to delete the blank rows.

In my case I set a range as above, you may need to or not

 

[CJ_London]

@Pat Hartman - OP is copying from one excel file to another, not into Access. I'll move the thread to the excel forum