saving every column of a sheet as a new sheet

[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 a 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

 

[Tom Urtis]

Stick this macro in a standard module, activate the source sheet with the columns you want to copy, and run the macro. It should approach what you are trying to do, in terms of getting the columns copied to their own worksheets prior to uploading into Access.

Sub ColumnSheetz()
Application.ScreenUpdating = False
Dim LC&, xCol&, asn$
LC = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
asn = ActiveSheet.Name
With Sheets(asn)
For xCol = 2 To LC
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Column_" & xCol
.Columns(1).Copy Columns(1)
.Columns(xCol).Copy Columns(2)
Next xCol
End With
Application.ScreenUpdating = True
End Sub

 

[DrOktagon]

Awesome! Thanks Tom. Works perfect.

Now I just need need to automate the process of importing every column as a separate table in Access