Using VBA to copy data from a closed workbook to the current

Hi Experts,

I need to use VBA code to get data from a closed workbook that is on a network folder, I only need the data that meets certain criteria.

ClosedFilePath = P:\FolderA\SubfolderA\Correos\
ClosedFileName = Registro_Correos_OficialesX.xlsm
ClosedFileSheet = Registros
ClosedFileRange = (A2 : L?) " this range is a table named Table1

CurrentFileName = Correos_Pendientes.xlsm
CurrentSheetName = "Pendientes"

So, I need to get all the data where Column(G) = "Administracion" and Column(K) = "Pendiente"

Thank you so much guys, attached is the source file and hope you guya can help me!
Who is Participating?
Harry LeeConnect With a Mentor Commented:

Try the following macro in Module 1 to see if it works for you.

Sub UpdateRecord()
Dim SourceWB As String, SourceFile As String, SourceWS As Worksheet, SourceRW As Integer, DestWS As Worksheet

SourceWB = "Registro-Correos-OficialesX.xlsm"

'Configure Destination Data Location
Set DestWS = Workbooks("Correos_Pendientes.xlsm").Sheets("Pendientes")
If DestWS.Cells(Rows.Count, 1).End(xlUp).Row >= 2 Then
    DestWS.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Select
End If

'Configure Source Data Location
SourceFile = "P:\FolderA\SubfolderA\Correos\" & SourceWB
Workbooks.Open Filename:=SourceFile
Set SourceWS = ActiveWorkbook.Sheets("Registros")

'Inspect Source Data Record and search for AdministracionPendiente
For SourceRW = 2 To SourceWS.Cells(Rows.Count, 1).End(xlUp).Row
    If SourceWS.Cells(SourceRW, 7) & SourceWS.Cells(SourceRW, 11) = "AdministraciĆ³nPendiente" Then
        Range(SourceWS.Cells(SourceRW, 1), SourceWS.Cells(SourceRW, 12)).Copy
        Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If

'Close Source Workbook
Workbooks(SourceWB).Close SaveChanges:=False

End Sub

Open in new window

[14-Sep-2013 15:05 UK time] Code added to comment for convenience of other members ~fp. (Microsoft Excel Topic Advisor)
elm9999Author Commented:

This is the source file
elm9999Author Commented:
Note: I will put the vba code in a button of the current file, so when I open it, I will click on it to bring the information.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

elm9999Author Commented:
Hi Harry,

Thank you so much for the code, it almost done, It works good but the only error message is in this line:


Run-time error '1004':

Delete method of Range class failed

Is it possible to use clear instead of delete?

Thank you again.
elm9999Author Commented:

Harry, I already replace the line of the error an put this:


And runs good.

Thank you so much for you help I really apreciate it :)
elm9999Author Commented:
Great help!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.