Hi,

I'm new to Macros and have no prior programming experience. I have 2 weekly files that always contains the same number of columns but different number of rows [ex. 1 week it will have 3K rows, another week it will have 2K rows, but always 6 columns]. I need to make the two sheets into table to do further analysis; can anyone please help?

I want to write a code that allows me to do the following in the following order:

1. Select only cells that contains data in sheet 1
2. make a table using data selected in sheet1
3. move to 'sheet2'
4. select only cells that contains data in 'sheet2'
5. make a table using the data in 'sheet2'

Thanks.

Hello,

I think that you could do this without the use of macros. If you use data connections to create a query table, you could update the sheets each week simply by refreshing the workbook. Here are the steps I took (XL 2007):

1. Data Get External Data From Other Sources From Microsoft Query
2. Select the Excel data source and click OK
3. Select the source workbook and click OK
4. If both sheets' tables show up in the list, click "Cancel" and then "Yes" to continue editing the query in Microsoft Query (if the tables don't show up, cancel and click No, then apply names to the data ranges and save to try and help the driver find the data)
5. Click "Close" and then click the SQL button to type in a SQL statement.
6. The SQL statement that I used (you'll need to change the file location and table names accordingly) was:

SELECT Table1.*
FROM `C:\Documents and Settings\Book1.xlsx`.Table1 Table1

UNION ALL

SELECT Table2.*
FROM `C:\Documents and Settings\Book1.xlsx`.Table2 Table2

7. Click OK to the message about not displaying the query graphically.
8. Click Return Data to Excel button, choose a location for your new table, and click OK.

Now, whenever your data changes, just make sure that any named ranges refer to the correct rows and refresh the workbook.

Hope this helps,

Ben