VBA retrieving data from closed workbooks

[rpmitchell]

I am attempting to copy sheets named "Lockbox" from various closed workbooks (source workbooks) into one Master workbook (destination workbook). I have found a macro that allows the user to select the files that I want to copy from, and supposedly paste the data into the new Master workbook. It then names the new sheet in the Master workbook extract1. The Macro seems to work, because I see the source books opening, but there is no data on the newly created sheet in the destination workbook. I want the macro to copy the entire sheet and into the destination workbook. The macro allows the user to keep selecting workbooks until they cancel, each time creating a new sheet in the destination workbook, naming it extract2, etc. I think the part of the code that's missing is the actual "copy" piece, where it knows what to copy and paste. I am new to VBA. Can someone please advise. Thanks. Here is the code:

Sub ValuesfromClosedWorkbook()
Dim filetoopen As String
Dim wb As Workbook
Dim ws As Worksheet

filetoopen = Application _
.GetOpenFilename("XL Files (*.xlsx), *.xlsx")
On Error Resume Next

Set wb = Workbooks.Open(filetoopen, True, True)
Set ws = ThisWorkbook.Worksheets.Add

With ws
.Cells.Value = wb.Worksheets("Lockbox").Cells.Value
End With
ws.Name = "extract1"

wb.Close False

filetoopen = Application _
.GetOpenFilename("XL Files (*.xls), *.xls")
On Error Resume Next

Set wb = Workbooks.Open(filetoopen, True, True)
Set ws = ThisWorkbook.Worksheets.Add

With ws
.Cells.Value = wb.Worksheets("Lockbox").Cells.Value
End With
ws.Name = "extract2"

 

[rpmitchell]

You are a Genius! It works perfectly! I'm an Accountant in Atlanta, GA and am just trying to automate some of the very redundant Excel tasks that our staff is performing. Thanks so much for your help!

 

[ZVI]

I'm glad it helped!
Cheers

 

[GlennUK]

Well done Vladimir.

I'm just curious ... I looked to find you in the Microsoft MVP list ( as per your signature ), and you don't appear to be there. Have they got your name spelling different to what your signature is?

 

[ZVI]

Well done Vladimir.

I'm just curious ... I looked to find you in the Microsoft MVP list ( as per your signature ), and you don't appear to be there. Have they got your name spelling different to what your signature is?

Hi Glenn,

I'm too lazy to register myself in Microsoft MVP list, or it's because of my false modesty - not sure

Was nominated by MVP Award Program Russia & CIS e-mail address removed
You may address to my MVP Leads Oksana Kostina-Panicheva e-mail address removed Yulia Belyanina e-mail address removed
or may be directly to Toby Richards (MS General Manager, Community & Online Support)
The term of my MS MVP Excel status lasts from 1 Jule 2010 and ends on 1 Jule 2011.

Kind Regards,
Vlad