Updating and Importing Excel workbooks macro

Microsoft Excel 2010 - license
October 4, 2010 at 15:58:17
Specs: Windows 7
I am trying to create a macro to find an excel workbook in a folder (Jan_01_10.xls, Jan_02_10.xls), and import 2 cells from each of those sheets to a separate file. Each file I need the same 2 cells (A2 and B6). Is there a way to do this so I don't have to continue to copy and paste these two valves? Also is there a way for the new file to make a new tab for each month (Jan on one tab and Feb on a separate tab). Thank you!!

See More: Updating and Importing Excel workbooks macro

Report •

October 4, 2010 at 17:00:28
I'm not quite sure what you mean by "find an excel workbook in a folder".

Do you mean that you have a specific file in a folder and that you are trying to pull 2 values from it each time you create a new workbook?

In addition, when you say "Also is there a way for the new file to make a new tab for each month" are you once again referring to a new file that you are creating and asking that each time you create the workbook it should have 12 tabs, one for each month?

Keep in mind that we can't see your workbook/project from where we're sitting, so you have to be very specific about what you are trying to do.

I think we can help you out...we just need a little more detail.

Report •

October 4, 2010 at 17:34:21
Thanks for the quick response! I'm glad your able to help!

I have several excel files located on a shared drive. They are located in the same folder labeled by the date (jan_01_10, jan_02_10, feb_01_10, ect). What I need the macro to do is to go into the folder, find the excel file (jan_01_10.exe), and import two cells to a separate report file. All of the dates need to be compliled into one worksheet. On all of the excel sheets in the folder on the shared drive, I need the same two cells imported.

I am trying to track daily production numbers per day and give an updating monthly average. Several individuals put data into daily tracking sheets. I need to compile all of this information into a common spreadsheet (which will end up being a graph) to let my department know how we are doing on a daily basis.

The second question I am asking is if at the end of the month, I can create a macro to start a new tab in the report workbook. For example a tab for January, February and March at the end of each month.

Thank you again

Report •

October 4, 2010 at 19:10:07
re: jan_01_10.exe

Don't you mean jan_01_10.xls?

Aside from that, you've basically said the same thing that you did in your first post:

You need the macro to "find the files". If the files are all in the same folder, why do you need to find them?

Are you saying that you have 365 files, one for each day of the year?

If you need to pull data from these files, why don't you just use a formula?

='C:\Documents and Settings\user_name\My Documents\[jan_01_10.xls]Sheet1'!$A$2

Report •

Related Solutions

Ask Question