Dean,
Depending on the location of this code, the unqualified Range objects may
refer to either the active sheet (if in a standard module) or the sheet that
holds this code, neither of which MAY be Worksheets("TEST").
It is always more clear for yourself (and others, later) and VBA to qualify
them. Assuming you mean:
With Worksheets("TEST")
MyArray = .Range("A2",.Range("A100").End(xlUp))
End With
'Notice the "." before the ranges
NickHK
- Show quoted text -
Hi Nick,
I have the following code. When I hard code the array with the values
the code works. When I try to make the array dynamic nothing
populates the array. Do I need to Redim the array? Is it a Ubound/
Lbound issue? I apologise as we have been over this a few times but I
cannot seem to get this to work. Dynamic arrays are proving difficult
for me to get. Please advise. Thanks for your patience.
Public Sub CreateCSV()
Dim Sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim MyArray As Variant
' MyArray = Array("23000-7000", "23000-7300", "23000-7040") THIS
WORKS
With Worksheets("TEST")
MyArray = .Range("A2", .Range("A100").End(xlUp))
End With
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)
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
