• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

copy c range set by a variable in Excel macro

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.
0
bobdeck
Asked:
bobdeck
  • 2
1 Solution
 
balajikumar75Commented:
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:= _
        "=Sheet2!R14C1"
In above example the name "rngAddresspaste" refers to cell starting "A14"

E.g to copy paste from Sheet1 to sheet 2.
*************************************
    Sheets("Sheet1").Select
    Range("A1:B2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    'Define range in excel
    ActiveWorkbook.Names.Add Name:="rngAddresspaste", RefersToR1C1:= _
        "=Sheet2!R14C1"

    Range("rngAddresspaste").Select
    ActiveSheet.Paste
*************************************

Please let me know if you need more details


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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now