Macro to copy data to a workbook from another workbook with a changing file name

**gruittbm** · 04-04-2012, 09:41 PM

Hi all,

I'm pretty new to Excel and VBA...hope somebody can help me with this:

I have built a workbook calculator to manage and sort data that comes in a weekly Excel file. As it is now, I have to copy and paste the data in the worksheet that I need to my workbook calculator and would like to automate the process with a macro. The problem I'm having is that the filename changes with the week that it comes in. For example...this week the file would be named "Weekly Data 4-06-2012" and next week the file would be named "Weekly Data 4-13-2012".

Is there a way to have the macro identify just the first couple of words in the file name and recognize that as the file that it needs?

Thanks so much for any help!!

Ben

**davesexcel** · 04-04-2012, 10:26 PM

This would be great, but you would have many workbooks that start with the first two words.
You could use a code such as

Please Login or Register  to view this content.

Then it would open the workbook with the name in the folder location with the name you had in Cell A1.

Change the folder location in the code.

**gruittbm** · 04-05-2012, 04:50 PM

That's not a bad idea, but the workbooks come once a week through Outlook and it's nothing that I have to save to a folder...the previous week's data gets saved over with the most current week in my active workbook. All I have to do is copy the cells from the entire worksheet into my workbook with all of the formulas in them. Is there a way to have Excel copy the data from the only other available open workbook besides my calculator? It wouldn't be as much of a problem if I didn't have to format the cells after that which I've figured out how to do with a macro.

Is there a macro like "GetOpenFile" that would help?

Thanks Dave!

**davesexcel** · 04-05-2012, 07:36 PM

Sure,
Try this to open the workbook.

Please Login or Register  to view this content.

**gruittbm** · 04-06-2012, 06:26 AM

The file is coming across as read-only so that macro won't read it unless I save it and turn read-only off...more steps than I was hoping for. If the file is already open from Outlook, I shouldn't need the "Folder" command, right? I did try to take that code and apply it to the recorded macro that wasn't working either but had the beginning elements that I was looking for:

Please Login or Register  to view this content.

I'm getting "Runtime Error 9: Subscript Out of Range" on the third line of code: Windows(File).Activate
I tried changing the syntax of the second and third lines to see if that was the problem but no go. Here are the variations I tried:

Please Login or Register  to view this content.

Any thoughts?

**davesexcel** · 04-06-2012, 06:44 AM

Maybe we need to loop through the open workbooks.

Please Login or Register  to view this content.

**gruittbm** · 04-06-2012, 07:16 AM

Thank you so much!!! I've been working on this for days now (as I said, I'm new to this)

Your code worked so I added it to what I already had:

Please Login or Register  to view this content.

Is there anything extraneous in there from our earlier attempts? I'm trying to learn all this on the fly.

**davesexcel** · 04-06-2012, 07:42 AM

Sure thing,
Instead of copying every cell in the worksheet, it just copies A1 to the last used cell.
This is done without having to select the workbook or worksheet.

You can use the worksheet name instead of the number if you want.

These example codes could become usefull to you in the future
http://www.davesexcel.com/vbacodes.htm#363409847

Please Login or Register  to view this content.

**gruittbm** · 04-09-2012, 07:38 PM

That's a nice resource for future problems on your website...I bookmarked it so I can finish reading through it.

Haven't had a chance to try the code you sent last week...hoping to get to it tonight or tomorrow. Just wanted to let you know I've seen it and appreciate all of your help.

Thanks Dave!

**gruittbm** · 04-11-2012, 05:28 PM

That worked like a charm...and it doesn't cause seizures like the recorded macro I was working with that activated worksheet after worksheet. I have a few more parts to this problem that I'd like to make this simple too, but I want to try to figure them out on my own first using the reference link you gave me above. Do you mind if I contact you if I get stuck there?

Thanks again for all of your help!!

Ben

Macro to copy data to a workbook from another workbook with a changing file name

LinkBack

Thread Tools

Rate This Thread

Display

Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Re: Macro to copy data to a workbook from another workbook with a changing file name

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions