VBA/Macros Help

[Bobbyleeds]

Hi Guys,
First time poster here.
I have an excel workbook that i am using as a form and have VBA to transfer data from that form to to a database workbook.
See code below
Public Sub copy_wb()

Dim copy_from As Range
Dim copy_to As Range

Set copy_from = Workbooks("Ideas Form_0 2").Worksheets(2).Range("B10:Y10")
Set copy_to = Workbooks("New Ideas Database").Worksheets(1).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

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

MsgBox ("Data added successfully!")

End Sub
The way to form works is by the user opening the excel from Sharepoint filling out the relevant info and then i have VBA to email a copy of this to a specific email address and to clear the form after this and they then close excel.

This works fine, the problem comes when trying to use the VBA above to transfer data to the database from the attached email copy as the file name gets given a version number when it is opened from the email, so 'Ideas Form_0 2' becomes 'Ideas Form_0 2 (004)' and therefore i think this makes the VBA not work properly.

Is there anything i can change in the code so that VBA doesn't take into account the version number (the number in brackets) when running the VBA so that it will always work (and transfer the data to the database) no matter how many copies are emailed to the email address?

Thanks in advance.

Robert

 

[Bobbyleeds]

Or is there and code that will transfer data from any workbook without needing a filename as I can just set it up to transfer data from the specific cells?

 

[Bobbyleeds]

or is there any code to attach and send a current active workbook via email but change the filename of the workbook before it is attached?