HUh? Don't understand why marco not working...

[Guanjin Peter]

Hi, can someone exlpain to me why isn't it working?
I tried to change
newWB2.Sheets("Order Template").Range("D79:F79").Select
to
newWB2.Sheets("Order Template").Range("R" & nTotal & "C:R" & nTotal & "C").Select

newWB2.Sheets("Order Template").Range("c3").Value = "=COUNTA(A:A)"
        nCus = newWB2.Sheets("Order Template").Range("c3").Value
        nTotal = nCus + 1
 
newWB2.Sheets("Order Template").Range("D79:F79").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        newWB2.Sheets("Sheet2").Delete
        newWB2.Sheets("Sheet3").Delete

 

[Smitty]

Looks like you're trying to use R1C1 notation when you can refer to the range directly:

newWB2.Sheets("Order Template").Range("D" & nTotal & ":F" & nTotal).Select

HTH,

 

[Guanjin Peter]

thanks for the reply...I was stuck in this situation...

The columns isn't fixed. That's why I'm using R1C1 notation, cos they automatically define the columns for you...
For example it always starts at column D, but may not always end at column F. If the data expands until X, it'll be
newWB2.Sheets("Order Template").Range("D" & nTotal & ":X" & nTotal).Select

I tried to replace X with a number, but the formula doesn't work. Any idea how can I get around with this?

 

[northwolves]

newWB2.Sheets("Order Template").[D1].offset(nTotal-1,0).resize(1,X-3).Select

Regards
Northwolves

 

[Guanjin Peter]

THanks the for reply!

Didn't start a new topic because it's linked.
How do I insert an empty row right after that?
is it...offset...then .value = "" ?

 

[northwolves]

THanks the for reply!

Didn't start a new topic because it's linked.
How do I insert an empty row right after that?
is it...offset...then .value = "" ?

newWB2.Sheets("Order Template").[D1].Offset(nTotal - 1, 0).Resize(1, X - 3).Select
Selection.Offset(1, 0).EntireRow.Insert Shift:=xlDown

Regards
Northwolves