With statement not working unless sheet is active

[DanGK1]

Hi

This is boggling me. Unless I make the sheets active this code does not work and I get run-time error 1004 Application-defined or object-defined error

Sub copysubclientsandsplit()
'
With Worksheets("Sub-Client Config")
    .range(range("A1"), range("A1").Offset(0, 2).End(xlDown)).Copy
End With
With Worksheets("Sub-Client Info")
    .range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    .range(range("A5"), range("A5").End(xlDown)).TextToColumns Destination:=range("A5"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True
     .Borders.LineStyle = xlContinuous
End With
End Sub

Any suggestions please?

Many thanks

 

[Jonmo1]

i think defining the lastrow earlier simplifies the code. am I correct ?

I guess that depends on your opinion of simple.
There is always more than 1 way to skin a cat, each accomplishes the task desired.
Which way is 'simpler' is subject to opinion and varies from one person to the next.

 

[DanGK1]

Here is the final code

Sub copysubclientsandsplit()
n = WorksheetFunction.CountA(Worksheets("Sub-Client Config").range("A:A"))
If n <= 1 Then
'do nothing
Else
'
With Sheets("Sub-Client Config")
    .range(range("A1"), .range("A1").Offset(0, 2).End(xlDown)).Copy
End With
With Sheets("Sub-Client Info")
    .range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    .range(range("A5"), range("A5").End(xlDown)).TextToColumns Destination:=range("A5"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True
End With
End If
End Sub

 

[DanGK1]

which didn't work...

this is the one that did!

Sub copysubclientsandsplit()
n = WorksheetFunction.CountA(Worksheets("Sub-Client Config").range("A:A"))
If n <= 1 Then
'do nothing
Else
'
With Sheets("Sub-Client Config")
    .range(.range("A1"), .range("A1").Offset(0, 2).End(xlDown)).Copy
End With
With Sheets("Sub-Client Info")
    .range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    .range(.range("A5"), .range("A5").End(xlDown)).TextToColumns Destination:=range("A5"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True
End With
End If
End Sub