Multiple Cells into txt file in specific order

Hi,

I'm new to the whole VB and macro thing, I can get my head around VlOOKUPS and IFs etc, but the programming in excel doesn't go in.

I used to write basic vba code for file manipulation, but was a long time ago, and my skills have faded.

Basically I need to create an xml file, but I'm happy to call it a .txt with the following output from excel.

I have 2 sheets with data that I need I will call them Sheet 3 and Sheet 4, however they are named something else but easier for others to follow if I can get an answer.

I need to get the data from these sheets in the following format:


All of column U from two sheets and export them into a txt file, then add a carriage return and add all of columns AD, AE, AF, AG so they look like this:

Output of Column U from Sheet3 and sheet4 all on new lines

Sheet3!U:1
Sheet3!U:2
Sheet3!U:3….UNTIL AT THE BOTTOM OF THE COLUMN
Sheet4!U:1
Sheet4!U:2
Sheet4!U:3….UNTIL AT THE BOTTOM OF THE COLUMN
<CarriageReturn>
Sheet3!AD:1
Sheet3!AD:2
Sheet3!AD:3…
Sheet4!AD:1
Sheet4!AD:2
Sheet4!AD:3….UNTIL AT THE BOTTOM OF THE COLUMN
Sheet3!AE:1
Sheet3!AE:2
Sheet3!AE:3…UNTIL AT THE BOTTOM OF THE COLUMN
Sheet4!AE:1
Sheet4!AE:2
Sheet4!AE:3….UNTIL AT THE BOTTOM OF THE COLUMN
Sheet3!AF:1
Sheet3!AF:2
Sheet3!AF:3…UNTIL AT THE BOTTOM OF THE COLUMN
Sheet4!AF:1
Sheet4!AF:2
Sheet4!AF:3….UNTIL AT THE BOTTOM OF THE COLUMN
Sheet3!AG:1
Sheet3!AG:2
Sheet3!AG:3…UNTIL AT THE BOTTOM OF THE COLUMN
Sheet4!AG:1
Sheet4!AG:2
Sheet4!AG:3….UNTIL AT THE BOTTOM OF THE COLUMN

END of file.

I hope this is possible somehow

L

Hi sorry to be a noob

but do I use Alt+F11, and if so where do I add the code? is it to workbook, or a sheet? I get an error when adding to ThisWorkBook says
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '9':

Subscript out of range
---------------------------
OK Help
---------------------------

thanks so much, nearly there!

I think my description is a little off!

And I cant quite read what you are getting it to do to reset it.

So I have the following columns
U - XML field1
AD - Start of XML field 2
AE - next part of 2
AF - next part of 2
AG - end part of 2

So I need all column U (500 odd rows) from sheet 3 and 4, followed by
AD row 1
AE row 1
AF row 1
AG row 1

AD row 2
AE row 2
AF row 2
AG row 2

AD row 3
AE row 3
AF row 3
AG row 3
All above AD to AG columns from sheet 3, then repeated sheet 4
End of file

nearly
that was much easier way to write it!!

----
Sheet3 U1
Sheet3 U2
Sheet3 U....
Sheet4 U1
Sheet4 U2
Sheet4 U.....
Sheet3!AD1
Sheet3!AE1
Sheet3!AF1
Sheet3!AG1
Sheet3!AD2....

Sheet4!AD1
Sheet4!AE1
Sheet4!AF1
Sheet4!AG1
Sheet4!AD2......

etc
---

Brilliant!!

Thanks for that, any way to remove the column headings and blank spaces if there are any? in the pages? no worries if not, I can do a search replace.