Modify code to import to all worksheets

**gnoke** · 07-28-2010, 04:16 AM

Hi,

I am using the following code to import all files in a directory.
Could someone please assist me in modifying the code, so that the imported files is pasted in all worksheets, not just the first (sheet1)?

Please Login or Register  to view this content.

Duplicate: http://www.mrexcel.com/forum/showthread.php?t=484275

Gnoke

**Bob Phillips** · 07-28-2010, 04:35 AM

Untested

Please Login or Register  to view this content.

**gnoke** · 07-28-2010, 04:40 AM

Thanks for replay!

I get the following error: "Invalid Next control variable reference" on line number 10 from the buttom "Next i"
Any ideas

?

**Bob Phillips** · 07-28-2010, 07:06 AM

I forgot to close my new loop

Please Login or Register  to view this content.

**gnoke** · 07-28-2010, 08:25 AM

Thanks again for your replay!

Now the macro runs, but I see no differens in the output compared to the prior macro.
The text files are not imported to any sheets except the first...

Ideas someone?

**gnoke** · 07-29-2010, 03:33 AM

Anyone else that have any ideas?
Would reallt appreciate any further assistance

**Bob Phillips** · 07-29-2010, 03:56 AM

Maybe I misunderstood the brief. Try this version

Please Login or Register  to view this content.

**gnoke** · 07-29-2010, 04:03 AM

Hi and thanks again Bob!

We are defininitly getting somewhere now, but still have two issues.
1. The macro imports to all sheets nicely, but only one text file, not all text files in the specified folder.
2. The single text file is also imported to a new workbook...

Gnoke

**gnoke** · 08-10-2010, 09:47 AM

Solution for me:

1. I use this code to import all text files into a single sheet:

Please Login or Register  to view this content.

2. Then i created new macros to delete rows with specific values in specific cells (or else delete rows with empty cells).
3. Then I recorded simple macros to e.g. arrange the rows ascending based text in column A.
4. In the end I recorded a macro while running all the simple macros.

This is probably not the most efficient for my job, but it does what I need

Thanks for all help!
Gnoke