How to Copy Data from Unknown Range with VBA

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”
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
‘Application.CutCopyMode = False

End Sub

Data in unknown range
Data in unknown range

Further Reading:

Worksheet UsedRange Property

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

One thought on “How to Copy Data from Unknown Range with VBA”

  1. Hi Mr.Dinesh

    I need help creating a code on Macros or VBA which can update current costs on a masterfile with the new costs from another worksheet which is produced on a monthly basis.

    The masterfile contains over 2000 products, and from columns F to T has information regarding the variable costs. Every month, the sales team produce a worksheet containing “new” costs for some of the products listed on the masterfile. These new costs have to be updated on the masterfile on an individual basis which is very time consuming.

    Is it possible to have a code which can look-up the data for the cost per product, compare it with the masterfile and update it on the masterfile? There is a condition: If the new costs are below the the current costs(on the masterfile), then this must not be updated.
    I have very little experience using VBA, and would appreciate any help provided!


Leave a Reply

Your email address will not be published. Required fields are marked *