Copy / paste

[shlobb]

hello gurus,

could someone help me tweak the below code, for whatever reason the data doesnt paste. i'm trying to avoid copy / paste to speed up the macro. TIA

Sheets("XXX").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.CurrentRegion.Value = Sheets("YYY").Range("a1").Value

 

[CalcSux78]

The "select" calls will slow more than copy paste. Try this method where the target range is discovered and then copied to new sheet.

Sub testcopy()
Dim sh As Worksheet
Dim Rng As Range
Dim RngAddress As String

Set sh = Sheets("XXX")
RngAddress = sh.Range("A1").End(xlToRight).Address
Set Rng = sh.Range(RngAddress).End(xlDown)
sh.Range("A1", Rng).Copy Sheets("YYY").Range("A1")
End Sub

 

[shlobb]

thanks CalcSux78, worked like a charm