Shorten VBA Code / Better way to write it

[hrayani]

Hi All,


Is there a way to shorten the below code...

Sub Macro4()'
' Macro4 Macro
'


'
    Range("B3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("B11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Regards,

Humayun

 

[Kyle123]

Possibly:

   With Range("B3").CurrentRegion
        .Copy Range("B11").Resize(.Rows.Count, .Columns.Count)
    End With

Though it depends on your data layout

 

[James006]

Hi,

You can test

Sub Macro4()
    Range("B3").CurrentRegion.Copy Destination:=Range("B11")
End Sub

Hope this will help

 

[hrayani]

Hello,

Thanks Kyle & james,

Both working perfect.

James i am gonna go with your one as it's only a single line

 

[hrayani]

two additions in the below code please

Range("B3").CurrentRegion.Copy Destination:=Range("B11")

1) if the file is another sheet - lets say sheet2
2) if the file is another folder - lets say d: drive folder:humayun file name:rayani sheet2

 

[hrayani]

i mean if the destination file is on another sheet or folder

 

[James006]

Hello again,

You need to explicit the destination ...

Both workbooks need to opened ...

Range("B3").CurrentRegion.Copy Destination:=Workbooks("rayani.xlsm").Sheet2.Range("B11")

Hope this will help