Hi,
I have run into a problem. I have two files e.g BRE2 master.xlsx & BRE2.xlsx. I open the second file from the first. All variables are correctly set (masterName, SheetNames, sourceFileName, TestVar1), I used the debugger to check this. When Workbooks(sourceFileName).Activate is called I get run time error 9 Index out of range. I don't under stand way? If I "hard set" the sourceFileName "BRE2.xlsx it works. If I have understood Error 9 correctly I have assigned something to that does not exist. Need some help to understand this problem. I'm new to VBA programming.
Best regards
Jonas
I have run into a problem. I have two files e.g BRE2 master.xlsx & BRE2.xlsx. I open the second file from the first. All variables are correctly set (masterName, SheetNames, sourceFileName, TestVar1), I used the debugger to check this. When Workbooks(sourceFileName).Activate is called I get run time error 9 Index out of range. I don't under stand way? If I "hard set" the sourceFileName "BRE2.xlsx it works. If I have understood Error 9 correctly I have assigned something to that does not exist. Need some help to understand this problem. I'm new to VBA programming.
Code:
Sub CaseCopy(masterName, SheetNames, sourceFileName, TestVar1)
' TestVar1 containes the last cell with a value on row 1 in master file
Dim TestVar2 As String 'Assign the value in the last cell on row 1
Dim r As Long 'Counter
Dim check As String
' Open Source file
Workbooks(sourceFileName).Activate 'Activate the source file ' [U]Here I get runtime error 9 Index out of range[/U]
' Activate appropriate Sheet
Worksheets(SheetNames).Activate
' Find the last cell with a value on row 1 in source file Assign TestVar2 a value, e.g. "Q3 2017", 2017
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For r = 3 To 100 Step 1
If IsEmpty(Worksheets(SheetNames).Cells(1, r).Value) = True Then
Worksheets(SheetNames).Cells("1,1").Activate
TestVar2 = Worksheets(SheetNames).Cells(1, r - 1).Value
Exit For
End If
Next
Jonas