Copy from a workbook without using path or file name

Hi
I have a master workbook in which I want to enter a line of competitor information from the many (Excel based) entry forms we receive. The entry forms come in having been saved under all different Team names.

The first row of a sheet within the entry form contains several columns of information that I want to transfer to the Master Sheet.

I have some code that copies the row from the entry form and successfully pastes it in to the correct sheet on the master workbook.

[Code]
Public Sub copy_wb()
Dim copy_from As Range
Dim copy_to As Range

Set copy_from = Workbooks(Test_Form).Worksheets(Test_Entry).UsedRange
Set copy_to = Workbooks(MASTER_ENTRY).Worksheets(TEST_PASTE).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

copy_from.Copy Destination:=copy_to
Application.CutCopyMode = False

End Sub
[\Code]

However, my problem is that the entry forms that I'm copying the information out of will not have consistent names or maybe even paths. Also, there may be many (40 or 50) entry forms in a folder at the end of a path.

If I have the Master open and open one of the entry forms would it be possible to make the copy_from find and copy from the entry form that is open on the desktop? i.e something along the lines of Copy_from Workbooks 1 = Desktop."*" ??

Not holding my breath, but it would be really useful if there is a way of doing this!

Thanks

This will loop through all open workbooks and copy from each one to the workbook that contains the code.

Please Login or Register  to view this content.

Alternatively, you could have it loop through all files in a folder or have it prompt the user to multi-select the files to copy from.

Thanks for the quick responses but we're not quite there yet!
With both the Master and one Entry form open I get a Run time 9 error - Subscript out of range warning with the "Set copy_from = Workbooks(1).Worksheets(Test_Entry).UsedRange" highlighted in yellow.
I should maybe add that it works if I put the actual entry form workbook name in instead of (1). Should the entry form be actually saved to the desk top, or doesn't it matter? What about if it was opened directly from arriving as an attachment to an email?

Put quotes around "Test_Entry" if that is the actual source sheet name.
Also, do you have any workbooks opened that do not have a "Test_Entry" sheet?

Please Login or Register  to view this content.

Try this. It's not tested, but it should copy all columns, omit the header row, and paste values.

Please Login or Register  to view this content.

Originally Posted by Frankie_The_Flyer

This works well with my test sheet (Test_Copy) apart from that the paste action into Test_Copy overwrites the last row of text in the sheet. It needs to paste one row lower please.

It should paste one row below in the last used cell in column B.
Is the cell in column B for the last row empty?

However, when I try it on the actual entry form I've been creating it doesn't copy anything. (Only the new form and the Master open).
Actual Code I have now is....

As a test, comment out the worksheet name If - End If

Please Login or Register  to view this content.