How to copy data from an unknown range in another workbook using the UsedRange property with VBA. We have learnt to copy data from one workbook to another and from one workbook to another in a specific worksheet. All the time we knew the exact range of the data to be copied. Let’s learn how to copy the data from an unknown range in a workbook sheet. Watch the video below:

Watch this video on YouTube.

Here’s the complete VBA code:

Sub myUsedRange()
Workbooks.Open Filename:=”C:\Users\takyar\copyData\copyPastefromUsedRange.xlsx”
Worksheets(“Sheet1”).Activate
ActiveSheet.UsedRange.Offset(1, 0).Copy
ActiveWorkbook.Close Save = False

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
‘ActiveWorkbook.Close
‘Application.CutCopyMode = False

End Sub

Further Reading:

Worksheet UsedRange Property