Hi,
Okay, here's a 'quick n dirty' way to give you some ideas.
So first I added some named ranges (Insert|Name|Define) as follows:
Code:
Name Defined As
ClinicDetails =ISOHdata!$A:$C
Demographics =ISOHdata!$D:$P
Eligibility =ISOHdata!$Q:$T
Other =ISOHdata!$AY:$BC
Treatments =ISOHdata!$U:$AX
Then, I amended the Export Row button's click event handler as follows:
Code:
Private Sub CommandButton2_Click()
Dim rngToExport As Range
With ListBox1
If .ListIndex > -1 Then
Set rngToExport = Worksheets(.List(.ListIndex, 1)).Range(.List(.ListIndex, 2))
Module6.ExportRow rngToExport
Else
MsgBox "Please select an item to export"
End If
End With
End Sub
I haven't checked how the listbox is populated, so you may need to add defensive coding in here if it may not return a valid worksheet/range address.
Then in module 6 I amended the foo sub to this:
Code:
Sub ExportRow(ByVal rngToExport As Range)
Dim vClinicDetails, vDemographics, vEligiblity, vTreatments, vOther
Dim ff As Long, c As Long
'put the relevant row data into 1-D arrays (tranpose 2x) so we can use Join()
With Application
vClinicDetails = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("ClinicDetails")).Value))
vDemographics = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("Demographics")).Value))
vEligiblity = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("Eligibility")).Value))
vTreatments = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("Treatments")).Value))
vOther = .Transpose(.Transpose(.Intersect(rngToExport.EntireRow, Range("Other")).Value))
End With
'get the next free file number
ff = VBA.FreeFile()
'open/create a text file which we want to send data to
Open "Thepathnameyouwantinhere\MyNewTextFile.text" For Output As ff
Print #ff, Join(vClinicDetails)
Print #ff, Join(vDemographics)
Print #ff, Join(vEligiblity)
Print #ff, Join(vTreatments)
Print #ff, Join(vOther)
'close the text file
Close ff
End Sub
Does that help?