March 26, 2018

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

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!


Comments are closed.