Hi..
That's potentially a LOT of values getting written to the text file..
How many columns in each sheet and how many sheets?
The reason i ask is I tried for an array based solution.. but.. the time it will take to execute will depend on how many columns your sheets contain and how many sheets..
You could try this.. it just might take a while depending on your answer to my questions above.. change the range used to suit..
Private Sub CommandButton1_Click()
Dim sh As Worksheet, Z, x, xx, i As Long, ii As Long
For Each sh In Sheets
'Setup the range yor working with
With sh.Range("A1:E" & sh.Range("A" & Rows.Count).End(xlUp).Row)
'Populate initial array
x = .Value
'Loop through array and populate new array
For i = LBound(x) To UBound(x)
For ii = LBound(x, 2) To UBound(x, 2)
If ii = UBound(x, 2) Then
xx = xx & vbCrLf
Else
xx = xx & x(i, ii)
End If
Next ii
Next i
End With
Next sh
'Create text file and write new array to it
With CreateObject("scripting.filesystemobject")
.CreateTextFile("D:\" & "Output" & ".txt").write xx
End With
End Sub
Bookmarks