Macro beginner help with making tables

[Kc001]

Hi,

I'm new to Macros and have no prior programming experience. I have
weekly files that always contains the same number of columns bu
different number of rows [ex. 1 week it will have 3K rows, another wee
it will have 2K rows, but always 6 columns]. I need to make the tw
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 th
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

 

[Ben McClave]

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 referto the correct rows and refresh the workbook.

Hope this helps,

Ben