Quick question regarding xlDown/xlToRight and .copy

[Jnrrpg11]

Hello Everyone,

Im trying to make my VBA code simpler and more efficient.

I am copy/pasting from one workbook called "ART" into the active workbook "FY Forecast Manager 2".
Previously i used the .Activate / .Select / .Selection.Copy method which worked.

Im trying to cut this code down to fewer lines now and the code i currently have is -


Sub ImportART()


Workbooks("Art.csv").Sheets("ART").Range("A1:BZ15000").Copy Sheets("FY Budget from ART").Range("A4")


End Sub


This code works but I've just stabbed at where the last row of the data will be (BZ15000).

In the select method i was using this code which worked -


Workbooks("ART.csv").Activate
Range("A1", Range("A1").End(xlToRight).End(xlDown)).Select
Selection.Copy


Workbooks("FYForecastManager2.xlsm").Activate
Worksheets("FY Budget from ART").Range("A4").PasteSpecial xlPasteValues



It seems when i try to combine the .End(xlToRight).End(xlDown) into the first sub it gives me a Runtime 1004 error.

Is it possible to use the first method and also use VBA to find the exact size of the data set?



Thanks in advance and Kind Regards

Richard

 

[Fazza]

hi, Richard

try range("A1").currentregion

regards, Fazza

 

[Fazza]

or for values only, what about?

With Workbooks("Art.csv").Sheets("ART").Range("A1")
    Sheets("FY Budget from ART").Range("A4").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

 

[Jnrrpg11]

Hello Fazza,

Thanks for taking the time to reply.
The first solution works perfectly, I've never seen the .CurrentRegion command before so this is going to be incredibly useful.


Thanks Again and Kind Regards

Richard

 

[Fazza]

good on you, Richard. Lots to learn. Go for it.