1

I am creating a way to read a CSV file, so for each line on it I am calling an exportCSV sub-routine. However it's happening a very strange thing on that routine, in order for the range object to work on the worksheet i need to activate it first, if i do not activate it it gives error "Run-time error 1004 - Methos Range of object _Worksheet failed" . Any idea why do I need to use the activate sheet method to stop the error ?

Sub exportCSV(rng As Range)
    Dim shtName As String
    Dim sht As Worksheet
    Dim lastLine As Long, newLine As Long
    Dim newRng As Range
    
    shtName = getSheetName(rng.Cells(1, 2).Value)
    Set sht = ThisWorkbook.Worksheets(shtName)
    If Not sht Is Nothing Then
        lastLine = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
        newLine = lastLine + 1
        sht.Activate 'If i comment this line a error is given on the line below
        Set newRng = sht.Range(Cells(newLine, 1), Cells(newLine, 19)) 'error on this line if no activate method is used
        Stop
    End If
End Sub

Private Function getSheetName(str As String)
    Dim word() As String, x As Long
    word = Split(str, Chr(32))
    x = UBound(word, 1)
    
    getSheetName = Trim(Replace(str, word(x), vbNullString))
End Function
Miguel
  • 109
  • 9
  • 2
    You need to qualify `Cells` - write `sht.Range(sht.Cells(newLine, 1), sht.Cells(newLine, 19))`. Else `Cells` will be used from the ActiveSheet. – FunThomas Jul 07 '21 at 15:09
  • ohh i see, that makes sense indeed... small mistake grrrr thanks – Miguel Jul 07 '21 at 15:16

0 Answers0