Copying Data from worksheets of workbook to another workbook with macro.

Guys/Gals,

I'd like some help with some automation to make my life easier, and I was wondering if it was possible.

Ok, each day, Monday through Friday, I get a workbook sent to my email, with current stats that I process in another workbook.

Currently, every morning I have the two workbooks open, I go to workbook 1/tab 1, press CTRL A (to make sure every cell is highlighted), CTRL C, got to workbook 2/tab1, press CTRL A (to make sure every cell is highlighted), CTRL V. Then rinse repeat, workbook1/tab2 over to workbook2/tab2, then workbook1/tab3 over to workbook2/tab3

Now, I was wondering, if in workbook2, I can create a macro (linked to a button) to go to c:\users\[ntlogin]\Downloads then look for a filename that is something like [Filename]_[date-email-sent]-[time-email-sent].xls. Now the [Filename] never changes, the [date-email-sent] changes based on the date and the [time-email-sent] does depend on when the system sends me the email, and open this workbook, and then provide the copy and paste (like I put above), and then close the workbook?

Hope someone may be able to help me with this little challenge.

Regards

Set a reference to MS Scripting Runtime and use code like this: Make the changes where indicated.

Please Login or Register  to view this content.

Thanks for the reply, I've tried and I've made changes to match, it gives me a msgbox pop up with the correct filename, but when I say yes it chucks up an error in the line

Set wkbkData = Workbooks.Open(strName)

saying the file doesn't exist? When I hover over the 'strName' in debug mode, the filename is correct, I'm wondering if I need to add the full path?

Ooops... my bad. I was using the current folder when testing.... use this:

Please Login or Register  to view this content.

That worked.... Thank you very much...Now, I'm probably going to push my luck here.... but, I'll ask as you seem very knowledgeable.

Lets just say, after I've copied over the tabs (which worked, as I tried it after the fix), on tab 3, column H (starting row 4) I have numbers, any numbers, but I want to replace Cell H4 downwards to maybe H4931 (which it is at, at the moment) from the number into =ROUNDDOWN(G4-F4,0).

Is that simple to do?

Very simple to do. Just add this before the End Sub line

Please Login or Register  to view this content.

Thank you so much for your help and time on this matter, this is going to save me so much time moving forwards.