make every column a new sheet and then import each as a separate table in Access

[DrOktagon]

Hello All,

I have been using my primitive knowledge to try and figure this out for a while and someone directed me here for help. Please help if you can. I'd like to be able to write a macro that does the following.

Saves every column of an Excel sheet as a new sheet (or perhaps a new book), but recopying the first column every time. So sheet2 will have columns 1&2, sheet 3 has columns 1&3, and so on.

Then (perhaps separate macro) imports each new sheet into Access as a separate table.

Reasons:
I have to split it up in excel first because my spreadsheets have ridiculously large numbers of characters in some cells (don't ask) and access has a limit on how many characters can imported in the row of an imported table.

Thanks

[JBeaucaire]

This is a little more versatile than you described, but it makes the macro work in any scenario you have like this.

The macro will:

1) ...Ask you to enter the first column NUMBER that is a data column. In your scenario, that would be column 2 (column B). All columns to the left of the number you enter will appear on every sheet created.

2) ...ask you how many data columns to put on each sheet. In your scenario, that would be 1 data column per sheet, but you might have other situations where you wanted data in column groups of 2 or more... this will handle that, too.

3) ...create a new sheet for each column group with the primary columns duplicated on each sheet.

Please Login or Register  to view this content.

Also published at:

Columns to Sheets