Dean,
- Most of your "ArrayDimension" function does nothing but copy values from
one array to another. You can achieve the whole this with a single line.
- Again your problem with MyArray is one of scope. Whilst you have declared
it at the module level, you have also declared (but not populated in any
way) it with the sub, at local level. So this local, empty variable is used.
Remove this local variable.
- Not sure what your code in "CreateCSV" is trying to do. What should be the
values of MyArray at this stage ? It will be a 2-D array.

NickHK

Option Explicit

Public MyArray As Variant

Public Function CSV()
ArrayDimension
CreateCSV
End Function

'You don't really need this in a function now, as it is only a single line
Public Function ArrayDimension()
MyArray = Worksheets(1).Range(Range("A2"), Range("A100").End(xlUp))
End Function

Public Sub CreateCSV()
Dim DestSh As Worksheet

'Dim MyArray As Variant

'Try to reference the desired sheet
On Error Resume Next
Set DestSh = ThisWorkbook.Worksheets("CSV")
'Return to default error handling
On Error GoTo 0

If DestSh Is Nothing Then
'No such sheet..
Application.ScreenUpdating = False
'So create it
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "CSV"
'..Not sure what you are doing here
'..
Application.ScreenUpdating = True
Else
'..Again not sure what you are doing here
'but something a bit different to the If block above ?
'..
End If

End Sub

NickHK

----------------------- CUT ----------------------------------

Hi Nick,

Sorry to get to this so late but was seconded off for a while. I have
declared the array as a public variable in a module and still cannot
get the array called in a sub later. I have both the codes below.
Please advise as I think that it is probably a rookie error on the
syntax somewhe

Public MyArray As Variant
Public Function CSV()
ArrayDimension
CreateCSV
End Function

Public Function ArrayDimension()
Dim rng As Range
Dim RangeValues As Variant
Dim i As Long
Dim j As Long
Dim a As Long


Set rng = Worksheets("TEST").Range("A2:A100")
ArrayCount = Excel.WorksheetFunction.CountA(rng)

ReDim MyArray(1 To ArrayCount, 1)

RangeValues = Worksheets("TEST").Range("A2:A" & ArrayCount + 1)

For i = LBound(RangeValues, 1) To UBound(RangeValues, 1)
For j = LBound(RangeValues, 2) To UBound(RangeValues, 2)
MyArray(i, j) = RangeValues(i, j)
Debug.Print MyArray(i, j)
Next
Next


End Function

Public Sub CreateCSV()

Dim Sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim MyArray As Variant

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("CSV").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "CSV"
For Each Sh In Sheets(MyArray)
Last = LastRow(DestSh)

With Sh.Range("A6:Q281")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
Set DestSh = ThisWorkbook.Worksheets("CSV")
For Each Sh In Sheets(MyArray(i, j))
Last = LastRow(DestSh)

With Sh.Range("A6:Q213")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True

End If

End Sub