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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Harry LeeCommented:

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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.