The code below works fine but my range to copy is dynamic,
so that in the below ("K3:R3") could be ("M3:S3")
I have defined my first and last columns by firstcol and lastcol
respectively.
so that instead of ("K3:R3") I want something lik
Range("(firstcol)3:(firstcol)3")

Many Thanks



Sheets("NATWEST").Select
Range("K3:R3").Copy
Sheets("MTS TOTAL").Select
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=Tru

--
T De Villier
-----------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647
View this thread: http://www.excelforum.com/showthread.php?threadid=56701

If firstcol and lastcol are strings containing column letters, then
Range(firstcol & "3:" & lastcol & "3")
If they are column numbers, first convert them to letters with this function:
Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), 1 -
(ColNumber 26))
End Function

firstclet=GetColLet(firstcol)
lastclet=GetColLet(lastcol)
Range(firstclet & "3:" & lastclet & "3")

Regards,
Stefi

€žT De Villiers€� ezt Ã*rta:


The code below works fine but my range to copy is dynamic,
so that in the below ("K3:R3") could be ("M3:S3")
I have defined my first and last columns by firstcol and lastcol
respectively.
so that instead of ("K3:R3") I want something like
Range("(firstcol)3:(firstcol)3")

Many Thanks



Sheets("NATWEST").Select
Range("K3:R3").Copy
Sheets("MTS TOTAL").Select
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=567018

Sheets("NATWEST").Select
Range(Cells(3,firstcol),Cells(3,lastCol)).Copy
Sheets("MTS TOTAL").Select
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"T De Villiers"
wrote in message
news:T.De.Villiers.2bv4rf_1154438407.9249@excelfor um-nospam.com...

The code below works fine but my range to copy is dynamic,
so that in the below ("K3:R3") could be ("M3:S3")
I have defined my first and last columns by firstcol and lastcol
respectively.
so that instead of ("K3:R3") I want something like
Range("(firstcol)3:(firstcol)3")

Many Thanks



Sheets("NATWEST").Select
Range("K3:R3").Copy
Sheets("MTS TOTAL").Select
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=567018

Similar to what has been suggested in the past:

Range(cells(3,firstcol),cells(lastcol,3)).Copy


or for a specific sheet (not the activesheet)

with worksheets("NATWEST")
.Range(.cells(3,firstcol),.cells(lastcol,3)).Copy
End With
Worksheets("MTS TOTAL").Range("C12").PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

--
Regards,
Tom Ogilvy


"T De Villiers" wrote:


The code below works fine but my range to copy is dynamic,
so that in the below ("K3:R3") could be ("M3:S3")
I have defined my first and last columns by firstcol and lastcol
respectively.
so that instead of ("K3:R3") I want something like
Range("(firstcol)3:(firstcol)3")

Many Thanks



Sheets("NATWEST").Select
Range("K3:R3").Copy
Sheets("MTS TOTAL").Select
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=567018