Referencing variable worksheet names contained in list on separate worksheet

[Viper147]

Hi guys,

I have been struggling for the past few days with the following and need some help please. I have a list of worksheet names (24 in total) on a main worksheet that I need to refer to in my macro. This list is variable based on selections made via ActiveX tickboxes that the user uses to choose the sheets that he wants to copy information to. However for some reason I cannot get the reference to the worksheet name/s to work. I keep on getting the "Subscript out of range" error on the line "Set MySheet = MyBook

.Worksheets("Sheet7")​

.
The external workbook opens up fine with no problems but then the macro stops on the line of code above. I initially tried to refer to the worksheet name which is reflected in a cell on the main worksheet where the macro is run from, and later to the worksheet number as is currently in the code but still no luck.
Thanks for the brilliant help as always.

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Import()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim a As Integer[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim DebtorsAge As Workbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim DebtorsPayments As Workbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim MySheet As Worksheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim msg As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]a = 3[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set MyBook = ActiveWorkbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Range("FalseCheck") = 24 Then MsgBox("There are no boxes ticked. Please check and try again."): Exit Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Do Until MyBook.Sheets("Data").Range("O"& a) = ""[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    IfMyBook.Sheets("Data").Range("O" & a) = True Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Set DebtorsAge= Workbooks.Open(FileName:=MyBook.Sheets("Data").Range("P"& a).Value)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Set MySheet =MyBook.Worksheets("Sheet7")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       Application.DisplayAlerts = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       DebtorsAge.Sheets(MyBook.Sheets("Data").Range("R"& a).Value).Range("A:P").Copy[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        MySheet.Range("A1").PasteSpecialxlPasteValues[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       DebtorsAge.Close False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    IfMyBook.Sheets("Data").Range("O" & a) = True Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        SetDebtorsPayments =Workbooks.Open(FileName:=MyBook.Sheets("Data").Range("Q"& a).Value)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       Application.DisplayAlerts = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       DebtorsPayments.Sheets(MyBook.Sheets("Data").Range("S"& a).Value).Range("A:AE").Copy[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       MySheet.Range("S1").PasteSpecial xlPasteValues[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       DebtorsPayments.Close False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]a = a + 1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Loop[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Len(msg) <> 0 Then MsgBox "The following fileswere not found in the folder:" & msg[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

 

[Viper147]

Hi all,

Please ignore my thread. I managed to resolve the problem.