TryingToLearn
Well-known Member
- Joined
- Sep 10, 2003
- Messages
- 730
Data entry workbook creates sheets with 1st 3 letters of the month then additional info. After data entry is complete, I'm trying to copy the worksheets for that month to a new workbook.
Got as far as creating an array with the correct sheet names but can't seem to get the syntax to copy to new workbook.
Also tried Sheets(Array(myarray)).Copy
either case gives error 9 subscript out of range which leads me to believe the sheet names are not in the correct format.
TIA
Got as far as creating an array with the correct sheet names but can't seem to get the syntax to copy to new workbook.
Code:
Sub rptCopy()
Dim Mnth As String
Dim ws As Worksheet
Dim myarray() As String
Dim x, N As Integer
Dim ShName As Variant
'------------------
Mnth = InputBox("Month to report (MMM) i.e. Feb")
ReDim Preserve myarray(0)
For Each ws In ThisWorkbook.Worksheets
If UCase(Left(ws.Name, 3)) = UCase(Mnth) Then
ReDim Preserve myarray(UBound(myarray) + 1)
myarray(UBound(myarray)) = ws.Name
x = x + 1
End If
Next
If x = 0 Then
MsgBox "No data found for " & Mnth & " report", vbInformation, "DATA NOT FOUND"
Exit Sub
End If
For N = 1 To x
Debug.Print myarray(N)
Next
Sheets(myarray).Copy
End Sub
Also tried Sheets(Array(myarray)).Copy
either case gives error 9 subscript out of range which leads me to believe the sheet names are not in the correct format.
TIA