copy c range set by a variable in Excel macro

Posted on 2006-04-17
Last Modified: 2010-05-18
I want to copy the first two columns in a worksheet "Input" starting with cell A1 into a worksheet "Calculations" starting in cell A14 the range of which is set by a variable in "Calculations!A12" because length of the columns varies on the length of the imported data.
Question by:bobdeck
    LVL 1

    Accepted Solution

    Excel allows you to define "name" at excel application level. These names point to a range in excel sheet. Using excel menu, you can define a name to range of cells by clicking on "Insert" -> "Name"  -> "Define"

    I can do same using macro something like this.
    ActiveWorkbook.Names.Add Name:="rngAddresspaste", RefersToR1C1:= _
    In above example the name "rngAddresspaste" refers to cell starting "A14"

    E.g to copy paste from Sheet1 to sheet 2.
        'Define range in excel
        ActiveWorkbook.Names.Add Name:="rngAddresspaste", RefersToR1C1:= _


    Please let me know if you need more details

    LVL 1

    Expert Comment

    Sure the above comments should have helped bobdeck. haven't heard since.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now