--- EDIT ---
After further digging I have found that the root cause is a function which increases a number at the end of a new sheet name if the desired name is already taken by an existing sheet - please scroll down to the 'EDIT' to cut right to the chase!
-----*-----
I'm trying to copy a range of cells from one sheet ("RIG") into another, newly created sheet. The new sheet's name has been assigned and the string NewestSheet holds this name as its value.
The range of cells has been defined within the string DataCopyRange.
All the variables have been defined as public outside of any subs and I have verified that they're pulling through throughout the process.
I'm trying to copy DataCopyRange, already defined as a certain range depending on what the integer rangetop is, to the newest sheet NewestSheet, however this results in the runtime error 9 "subscript out of range" in the row highlighted below When I was trying to fix this, I replaced NewestSheet with the original sheet "RIG" which copied the cells perfectly.
I have included the section of code, but having fiddled around with the strings and ranges, I have figured out that the problem is coming only from the last line:
Sheets("Sampling Report").Visible = True
Sheets("Sampling Report").Copy After:=Worksheets(Worksheets.Count)
ActiveWindow.ActiveSheet.Name = NewDataName
NewestSheet = NewDataName
If rangetop = 0 Then
errnorangetop = MsgBox("An error occured. Range max = " & rangetop, vbCritical, "Error")
Exit Sub
End If
'Select area of data to copy
DataCopyRange = "C17:G" & rangetop
'Copy data from RIG tab - this is the line with the error!
Worksheets("RIG").Range(DataCopyRange).Copy Worksheets(NewestSheet).Range("A10")
(Note NewDataName is from a function that assigns the new sheet a name, here I have assigned a string the same value as what the function returns just to keep things apart)
I have already consulted lots of other answers about error 9 subscript out of range when copying data to new worksheets, and have tried some of the answers suggested but haven't managed to fix it yet.
The error is almost definitely coming from NewestSheet, however I have iterated all the existing worksheet names through the debug and the correct one comes out in the debug. It should be the exact same value in this string.
I have determined that both strings in the final line show what they should, in this case:
DataCopyRange is "C17:G23" (string)
NewestSheet pulls through the value that the NewDataName function gave it, "Sampling Report 27-10-2022 1" (string)
I'm only working in one workbook and have no others open.
------------- EDIT -------------
Further digging has found that the problem is likely coming from the function, it seems to be adding a 1 to the tab name (the idea is that it checks to see if the name already exists, and increases the final number to 2, 3 etc).
Not sure why/how it's doing this as when I run the debug there still only seems to be the one sheet ending in '1'. I'd like to keep in the i = i 1 somewhere though so that it will increase the 1 to a 2 if a tab already exists with the name and a 1.
Not sure how to fix it so it doesn't increase the 1 to a 2 before sending the name to the Sub above:
Private Function NewDataName() As String
Dim ws As Worksheet
Dim i As Long: i = 1
Dim shtname As String
Dim shortdate As String
shortdate = Format(Date, "dd-mm-yyyy")
Do
' Create a worksheet name
shtname = "Sampling Report " & shortdate & " " & i
' Check if we already have a worksheet with that name
On Error Resume Next
Set ws = ThisWorkbook.Sheets(shtname)
On Error GoTo 0
'If no worksheet with that name then return name
If ws Is Nothing Then
NewDataName = shtname
Exit Do
Else
i = i 1
Set ws = Nothing
End If
Loop
End Function
Any ideas?
CodePudding user response：
Try breaking it down a little so it's easier to see where the problem is:
EDIT2: this works for me as-is (assuming no hidden sheet as noted below)
Sub Tester()
Dim rangetop As Long, newName As String
Dim wb As Workbook, wsSR As Worksheet, wsNew As Worksheet, wsRig As Worksheet
Set wb = ThisWorkbook 'specify a workbook
Set wsRig = wb.Worksheets("RIG")
Set wsSR = wb.Worksheets("Sampling Report")
wsSR.Visible = True
wsSR.Copy After:=wb.Worksheets(wb.Worksheets.Count)
Set wsNew = wb.Worksheets(wb.Worksheets.Count) 'get a reference to the copy
wsNew.Name = NewDataName(wb) 'call the function
rangetop = 25
If rangetop = 0 Then
MsgBox "An error occured. Range max = " & rangetop, vbCritical, "Error"
Exit Sub
End If
Worksheets("RIG").Range("C17:G" & rangetop).Copy wsNew.Range("A10")
End Sub
Function NewDataName(wb As Workbook) As String
Dim i As Long, root As String
root = "Sampling Report " & Format(Date, "dd-mm-yyyy") & " "
i = 1
Do While SheetExists(wb, root & i)
i = i 1
Loop
NewDataName = root & i
End Function
Function SheetExists(wb As Workbook, nm As String) As Boolean
On Error Resume Next
SheetExists = wb.Worksheets(nm).Name = nm
End Function
EDIT: you can't call the Worksheet.Copy method and pass a hidden worksheet as the argument for the After parameter: if you do that then the copy will not end up at the expected index.
Eg. if the last worksheet "xxx" in wb is hidden then this:
ListSheets wb
wsSR.Copy After:=wb.Worksheets(wb.Worksheets.Count)
ListSheets wb
gives output:
----------------------------
1 RIG visible
2 Sampling Report visible
3 xxx hidden
----------------------------
1 RIG visible
2 Sampling Report visible
3 Sampling Report (2) visible
4 xxx hidden
Note where the copy ended up. If you have hidden sheets then move them to the "front" of the workbook to avoid this problem.
ListSheets:
Sub ListSheets(wb As Workbook)
Dim i As Long
Debug.Print "----------------------------"
For i = 1 To wb.Worksheets.Count
With wb.Worksheets(i)
Debug.Print i, .Name, , IIf(.Visible, "visible", "hidden")
End With
Next i
End Sub