Dynamic Range

[T De Villiers]

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)3firstcol)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

 

[Guest]

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:

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

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