Subscript Out of Range.

[HelloLloyd]

I keep getting this error (Subscrip out of range.)when I try to run the code below. Everything should be in the code that you would need to help. I've had this problem before when I try to reference a range using a variable for the parameter. Is there any way to do that. I really don't want to have to type in all the ranges that I need to copy and paste. Dynamic would be great. Thanks in advance for the help. Here is my code...

Public destination As String
Public source As String
Public n As String

Sub Main()
Call GetCurrentFilename
Call GetDataFilename
Call CopyData
End Sub
Sub GetCurrentFilename()
Dim p As String
Dim n As String

p = ActiveWorkbook.path
n = ActiveWorkbook.name

destination = p + "\" + n

MsgBox (destination)
End Sub

Sub GetDataFilename()
source = InputBox("Please input the data file path and file name:", "Open Data File&quot
Application.Workbooks.Open filename:=source
End Sub


Sub CopyData()
Dim i As Integer
Dim j As Integer
Dim VarRange1 As String
Dim VarRange2 As String
Dim vr11 As Integer
Dim vr12 As Integer
Dim vr21 As Integer
Dim vr22 As Integer

j = 1
vr11 = 4
vr12 = 13
vr21 = 16
vr22 = 19
VarRange1 = ("g4:dv13&quot
VarRange2 = ("g16:dv19&quot
For i = 1 To 7
Select Case j
Case 1
Application.Workbooks(source).Worksheets("Lot 1&quot.range(VarRange1).Copy Application.Workbooks(destination).Worksheets.range(VarRange1)
Application.Workbooks(source).Worksheets("Lot 1&quot.range(VarRange2).Copy Application.Workbooks(destination).Worksheets.range(VarRange2)
Case 2
Application.Workbooks(source).Worksheets("Lot 2&quot.range(VarRange1).Copy Application.Workbooks(destination).Worksheets.range(VarRange1)
Application.Workbooks(source).Worksheets("Lot 2&quot.range(VarRange2).Copy Application.Workbooks(destination).Worksheets.range(VarRange2)
Case 3
Application.Workbooks(source).Worksheets("Lot 3&quot.range(VarRange1).Copy Application.Workbooks(destination).Worksheets.range(VarRange1)
Application.Workbooks(source).Worksheets("Lot 3&quot.range(VarRange2).Copy Application.Workbooks(destination).Worksheets.range(VarRange2)
Case 4
Application.Workbooks(source).Worksheets("Lot 5&quot.range(VarRange1).Copy Application.Workbooks(destination).Worksheets.range(VarRange1)
Application.Workbooks(source).Worksheets("Lot 5&quot.range(VarRange2).Copy Application.Workbooks(destination).Worksheets.range(VarRange2)
Case 5
Application.Workbooks(source).Worksheets("Lot 6&quot.range(VarRange1).Copy Application.Workbooks(destination).Worksheets.range(VarRange1)
Application.Workbooks(source).Worksheets("Lot 6&quot.range(VarRange2).Copy Application.Workbooks(destination).Worksheets.range(VarRange2)
Case 6
Application.Workbooks(source).Worksheets("Lot 7&quot.range(VarRange1).Copy Application.Workbooks(destination).Worksheets.range(VarRange1)
Application.Workbooks(source).Worksheets("Lot 7&quot.range(VarRange2).Copy Application.Workbooks(destination).Worksheets.range(VarRange2)
Case 7
Application.Workbooks(source).Worksheets("Lot 8&quot.range(VarRange1).Copy Application.Workbooks(destination).Worksheets.range(VarRange1)
Application.Workbooks(source).Worksheets("Lot 8&quot.range(VarRange2).Copy Application.Workbooks(destination).Worksheets.range(VarRange2)
End Select
j = j + 1
vr11 = vr11 + 19
vr12 = vr12 + 19
vr21 = vr21 + 19
vr22 = vr22 + 19
VarRange1 = "g" + vr11 + ":dv" + vr12
VarRange2 = "g" + vr21 + ":dv" + vr22
End Sub

 

[briand2]

In your code, you refer to "Worksheets", yet you have already set variable "n" thus: "n = ActiveWorkbook.name".

It is unlikely (?) that the name of the Worksheet is the same as the name of the workbook, so my guess that is why you are getting the subscript out of range.

Hope this helps.

Brian

 

[HelloLloyd]

You were right, my logic wasn't correct, but I've made the needed changes, double checked the values, and I still get the subscript out of range error...

 

[TonyJollans]

Hi HelloLloyd,

I think your problem stems from using path names to qualify your workbook names. You should use whatever the Excel application is using to reference the workbook.

So if ActiveWorkbook.Name = "Destination.xls" then to reference it you should use Workbooks("Destination.xls&quot and NOT Workbooks("C:\My Documents\Destination.xls&quot.

Enjoy,
Tony