+ Reply to Thread
Results 1 to 4 of 4

copy values from current area to other area using indirect addressing

Hybrid View

  1. #1
    Registered User
    Join Date
    11-12-2012
    Location
    Kaunas
    MS-Off Ver
    Excel 2010
    Posts
    6

    copy values from current area to other area using indirect addressing

    I have form with check boxes - it's current area. I fill the form and I want to save the values (let's say it is A1:P50) to store!A1:P:50 the same workbook - storage area.
    Is it possible to use target addressing in the cell?
    I see this like that:
    1. I fill the form, it is the ticking check boxes and automated change of values in the area current!A1:P50
    2. write address in the cell current!R1. The address is store!A1:P:50 at this time
    3. execute a macro. Macro makes copy values from current!A1:P50 to store!A1:P:50 using value of current!R1 as target address.

    How to use cell value in the macro?

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: copy values from current area to other area using indirect addressing

    attach a sample file with comments
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    11-12-2012
    Location
    Kaunas
    MS-Off Ver
    Excel 2010
    Posts
    6

    sample excel workbook

    macro looks like that:
    Sub copy1()
    ' copy values
    Range("A1:P50").Select
    Selection.copy
    Sheets("store").Select

    ' I need to take "Range" from current!R1

    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

    I need to take target range from the cell current!R1. I want to use variable range
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-12-2012
    Location
    Kaunas
    MS-Off Ver
    Excel 2010
    Posts
    6

    solution

    the macro:
    Sub copy_04()
    ' copy_1
    Dim MyWs As Worksheet
    Dim area As Variant
    Set MyWs = Worksheets("current")
    area = MyWs.Range("R1").Value
    Sheets("current").Select
    Range("A1:P50").Select
    Selection.copy
    Sheets("store").Select
    Range(area).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

    takes value of cell current!R1 as target for pasting copied values.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1