Need a macro for separating data in a worksheet

[charlotteparr]

Hi,

im new to the forum so dont laugh if this is a really simple problem! I have a macro set up to import 4000 text files into worksheet but they all insert into 1 column. I need each data set in a new column (each set is 46 rows).

I was wondering if somebody could help me with a macro which will select each data set and paste it into the next free column therefore having 46 rows from row 6 to row 52.

This would be much appreciated as ive spent at least 6 hours trying to make it work!

Thanks

[Mordred]

I think you may need to upload an example workbook. It sounds like a simple Text to Columns procedure but without seeing the data it is a guessing game.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Alf]

I assume you use a macro along these lines to import your text files so with a slight modification of the destination using cell coordinates should work.

Please Login or Register  to view this content.

You will have to "Dim i as An Integer"

Your problem will be importing values from 4000 text files as excel 2003 (your profile says Excel 2003) has a limit of 256 columns. Excel 2007 on the other hand should be able to handle 16000.

Alf

[charlotteparr]

example.xlsm

Hope this helps!

[Leith Ross]

Hello charlotteparr,

Welcome to the Forum!

To help develop a solution, yyou should also post a text file that be used for testing.

[charlotteparr]

Test.3fibluvk.000005.txtTest.3fibluvk.000006.txtTest.3fibluvk.000007.txtTest.3fibluvk.000008.txtTest.3fibluvk.000009.txt

Attached are sample txt files, just a handful of the 4000!

[Leith Ross]

Hello charlotteparr,

I have added the macro below to the attached workbook. the code is in Module2.

Please Login or Register  to view this content.

[charlotteparr]

Works perfectly

thank you so much! i can see where i was going wrong now!

[Leith Ross]

Hello charlotteparr,

You'er welcome. If you have any questions about the code, just ask. I will be happy to explain.